Microsoft Office Tutorials and References
In Depth Information
Session 2.1
The starting point of the range in the formula needs to be fixed at the cell that contains
the net income for January, cell B24. To be sure that the formula points to cell B24, you need
to use the absolute reference \$B\$24. The ending cell of the range will shift as you copy the
formula to the other months in the worksheet. You need to use a relative reference for the
ending cell in the range so that Excel will adjust the reference as the formula is copied. The
formula for the running total through the first two months will be =SUM(\$B\$24:C24) . When
you paste this formula to the other months of the year, Excel will adjust the cell range to cal-
culate the total for all of the months up to that point.
To calculate the running total using an absolute reference to cell B24:
1. Click cell A25 , type Running Total , and then press the Tab key twice to move to column C.
2. Type =SUM(B24:C24) in cell C25, and then press the Enter key. Excel displays the value –973,
showing that the family’s expenses exceed their income by \$973 through the first two months
of the year.
Now you’ll change the formula to use an absolute reference for cell B24 by selecting it in
the formula and pressing the F4 key.
3. Double-click cell C25 to switch to edit mode, and then double-click B24 within the formula
to select the cell reference.
4. Press the F4 key to change the cell reference from B24 to \$B\$24. See Figure 2-7.
Figure 2-7
Entering an absolute reference
absolute reference entered by pressing the F4 key
Trouble? If you pressed the F4 key too many times and passed the absolute reference,
continue pressing the F4 key to cycle through the options until \$B\$24 is displayed in
the formula.
5. Press the Enter key when the correct reference is displayed. Excel displays the value –973.
Now you can copy this formula to the remaining months of the year.
6. Click cell C25 , and then click the Copy button
on the Standard toolbar. The moving
border indicates that cell C25 has been copied.
7. Select the range D25:M25 , and then click the Paste button on the Standard toolbar.
Excel copies the formula to the remaining cells, as shown in Figure 2-8. The amount shown
for each month represents the cash on hand that the family accumulated during the year,
up to and including that month. So, for example, at the end of the year, after paying all
expenses, they have a total of \$6,396.

Search JabSto ::

Custom Search