Microsoft Office Tutorials and References

In Depth Information

**Creating Formulas**

But let’s face it: nobody particularly likes math,

especially complex math that involves several

calculations, each dependent on the last result

being correct. For example, after calculating the

Second Quarter sales total for the Northwest

Region, you might want to add that total to the

Second Quarter totals for the Midwest, Western,

and Southern Regions so you can see how well

the company is doing overall. If you were

calculating this by hand (or even with a calculator),

and you made a mistake in calculating the

Northwest Region’s sales total, then the company

total would be wrong as well. With Excel, you

can easily create one total and then reference

that total in another formula, without having to

reenter the formula for calculating the first total

all over again.

If you make changes to your data, Excel

automatically recalculates your formulas. For

example, if you change the May sales total for the

Northwest Region from $110,250 to $112,750,

then the second quarter sales total changes

automatically from $329,850 to $332,350 (for

example). Excel can handle anything from simple

calculations, such as adding two numbers together,

to complex calculations, such as adding only

the May sales values from each salesperson and

dividing by the total number of salespeople to

calculate the average sales amount for May.

To create a formula,
you type it into

a cell. After you press Enter, the result of the

calculation is displayed (rather than the formula

itself). If you click the cell, the formula you typed

appears in the Formula bar (see Figure 2-2). All

formulas begin with an equals (=) sign, and

typically include a reference to one or more cells.

The values in the referenced cells are used when

calculating the formula result. These cell values

can be static (meaning they don’t change) or the

result of another formula. For example, in cell

E12, you might enter a formula that adds monthly

sales for the Northwest Region to calculate the

Second Quarter sales total. In cell E16, you

might enter a different formula that references

the value in cell E12, adding it to the Second

Quarter totals for the other regions. If you have

to change the Northwest Region’s May sales

amount later on, the total in cells E12 and E16

are automatically recalculated for you.