Excel Lecture 7

Understanding Excel Formulas

One way to add calculations to an Excel workbook is to create your own formulas. Formulas are typically used to perform calculations such as addition, subtraction, multiplication, and division. More complex calculations are better left to Excel functions, which is a built-in set of formulas that provide financial, mathematical, and statistical calculations.

Formulas that you create typically include cell addresses that reference cells on which you want to perform a calculation. Formulas also consist of mathematical operators, such as + (addition) or * (multiplication).

Formula Operators

Operator	Performs	Sample Formula	Result
^	Exponentiation	=A1^3	Enters the result of raising the value in cell A1 to the third power
+	Addition	=A1+A2	Enters the total of the values in cells A1 and A2
–	Subtraction	=A1–A2	Subtracts the value in cell A2 from the value in cell A1
*	Multiplication	=A2*A3	Multiplies the value in cell A2 by cell A3
/	Division	=A1/B1	Divides the value in cell A1 by the value in cell B1

Order of Operations

The order of operations, or operator precedence, simply means that some operations take precedence over other operations in a formula. For example, in the formula =C2+D2*E2, the multiplication of D2 times E2 takes precedence, so D2 is multiplied by E2 and then the value in cell C2 is added to the result.

You can force the precedence of an operation by using parentheses. For example, if you want C2 and D2 added before they are multiplied by E2, the formula would have to be written =(C2+D2)*E2.

The natural order of math operators follows:

1.      Exponent (^) and calculations within parentheses

2.      Multiplication (*) and division (/)

3.      Addition (+) and subtraction (–)

Entering Formulas

You can enter formulas in one of two ways: by typing the entire formula, including the cell addresses, or by typing the formula operators and selecting the cell references. Take a look at both ways.

To type a formula, perform the following steps:

1.      Select the cell where you will place the formula.

2.      Type an equal sign (=) into the cell to begin the formula.

3.      Enter the appropriate cell references and operators for the formula. The formula also appears in the Formula bar as you type it. The cells that you specify in the formula are highlighted with a colored border.

4.      Press Enter when you have finished the formula, and Excel calculates the result.

Unwanted Formula If you start to enter a formula and then decide you don’t want to use it, you can skip entering the formula by pressing Esc.

To enter a formula by selecting cell addresses, follow these steps:

1.      Click in the cell where you will place the formula.

2.      Type the equal sign (=) to begin the formula.

3.      Click the cell whose address you want to appear first in the formula. You can also click a cell in a different worksheet or workbook. The cell address appears in the cell and in the Formula bar.

4.      Type a mathematical operator after the value to indicate the next operation you want to perform. The operator appears in the cell and in the Formula bar.

5.      Continue clicking cells and typing operators until the formula is complete.

6.      Press Enter to accept the formula and have Excel place its results into the cell.

Using the Status Bar AutoCalculate Feature

Using a feature that Excel calls, you can view the sum of a column of cells simply by selecting the cells and looking at the status bar. The values in the selected cells are added. You can also right-click the AutoCalculate area of the status bar and choose different formulas, such as average, minimum, maximum, and count.

Editing Formulas

1.      Select the cell that contains the formula you want to edit.

2.      Click in the Formula bar to place the insertion point in the formula, or press F2 to enter Edit mode (the insertion point is placed at the end of the entry in that cell).

3.      Press the left-arrow key or the right-arrow key to move the insertion point within the formula. Then, use the Backspace key to delete characters to the left, or use the Delete key to delete characters to the right. Type any additional characters.

4.      When you finish editing the data, click the Enter button on the Formula bar or press Enter to accept your changes.

Advertisement