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