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.