Fabricating Those Fabulous Formulas in Excel

Formulas are the real workhorses of the Excel worksheet. If you set up a formula properly, it computes the right answer when you enter it into a cell. From then on, it keeps itself up-to-date, recalculating the results whenever you change any of the values that the formula uses.

You let Excel know that you're about to enter a formula (rather than some text or a value) in the current cell by starting the formula with the equal sign (=). Most simple formulas follow the equal sign with a built-in function such as SUM or AVERAGE. Other simple formulas use a series of values or cell references that contain values separated by one or more of the following mathematical operators:
+ (plus sign) for addition
– (minus sign or hyphen) for subtraction
* (asterisk) for multiplication
/ (slash) for division
^ (caret) for raising a number to an exponential power

For example, to create a formula in cell C2 that multiplies a value entered in cell A2 by a value in cell B2, enter the following formula in cell C2: =A2*B2.

To enter this formula in cell C2, follow these steps:

1. Select cell C2.

2. Type the entire formula =A2*B2 in the cell.

3. Press Enter.

or

1. Select cell C2.

2. Type = (equal sign).

3. Select cell A2 in the worksheet by using the mouse or the keyboard.

This action places the cell reference A2 in the formula in the cell.

4. Type * (Shift+8 on the top row of the keyboard).

The asterisk is used for multiplication rather than the X symbol you used in school.

5. Select cell B2 in the worksheet by using the mouse or the keyboard.

This action places the cell reference B2 in the formula.

6. Click the Enter box to complete the formula entry, while at the same time keeping the cell pointer in cell C2.

Excel displays the calculated answer in cell C2 and the formula =A2*B2 in the Formula bar (as shown in Figure 1).

When you finish entering the formula =A2*B2 in cell C2 of the worksheet, Excel displays the calculated result, depending on the values currently entered in cells A2 and B2. The major strength of the electronic spreadsheet is the capability of formulas to automatically change their calculated results to match changes in the cells referenced by the formulas.

Now comes the fun part: After creating a formula like the preceding one that refers to the values in certain cells (rather than containing those values itself), you can change the values in those cells, and Excel automatically recalculates the formula, using these new values and displaying the updated answer in the worksheet! Using the example shown in Figure 1, say that you change the value in cell B2 from 100 to 50. The moment that you complete this change in cell B2, Excel recalculates the formula and displays the new answer, 1000, in cell C2.

Many formulas that you create perform more than one mathematical operation. Excel performs each operation from left to right according to the mathematical order of operations. In this order, multiplication and division pull more weight than addition and subtraction and, therefore, are performed first, even if these operations don't come first in the formula (when reading from left to right).

Consider the series of operations in the following formula:
=A2+B2*C2

If cell A2 contains the number 5, B2 contains the number 10, and C2 contains the number 2, Excel evaluates the following formula:
=5+10*2

In this formula, Excel multiplies 10 times 2 to equal 20 and then adds this result to 5 to produce the result 25.

If you want Excel to perform the addition between the values in cells A2 and B2 before the program multiplies the result by the value in cell C2, enclose the addition operation in parentheses as follows:
=(A2+B2)*C2

The parentheses around the addition tell Excel that you want this operation performed before the multiplication. If cell A2 contains the number 5, B2 contains the number 10, and C2 contains the number 2, Excel adds 5 and 10 to equal 15 and then multiplies this result by 2 to produce the result 30.