Microsoft Office Tutorials and References

In Depth Information

**Chapter 3: Formulas and Functions for Crunching Numbers**

As the figures in the Actual Income column (column C) are updated, figures

in the Over/Under Budget column (column E) and the Total Income row

(row 8) change instantaneously. These figures change instantaneously because

the formulas refer to the numbers in cells, not to unchanging numbers

(known as
constants
).

Figure 3-3 shows the formulas used to calculate the data in the worksheet in

Figure 3-2. In column E, formulas deduct the numbers in column D from the

numbers in column C to show where the PTA over- or under-budgeted for

the different sources of income. In row 8, you can see how the SUM function

is used to total cells in rows 3 through 7. The end of this chapter explains

how to use functions in formulas.

Figure 3-3:

The

formulas

used to

generate

the numbers

in Figure 3-2.

Book III

Chapter 3

Excel is remarkably good about updating cell references in formulas when

you move cells. To see how good Excel is, consider what happens to cell

addresses in formulas when you delete a row in a worksheet. If a formula

refers to cell C1 but you delete row B, row C becomes row B and the value

in cell C1 changes addresses from C1 to B1. You would think that references

in formulas to cell C1 would be out of date, but you would be wrong. Excel

automatically adjusts all formulas that refer to cell C1. Those formulas now

refer to cell B1 instead.

In case you’re curious, you can display formulas in worksheet cells instead

of the results of formulas, as was done in Figure 3-3, by pressing Ctrl+’

(apostrophe) or clicking the Show Formulas button on the Formulas tab (you may

have to click the Formula Auditing button first, depending on the size of

your screen). Click the Show Formulas button a second time to see formula

results again.