Microsoft Office Tutorials and References

In Depth Information

**Developing a Savings Plan**

◗

5.
Select the range
B4:C4
, and then drag the fill handle to cell
. The values
G4

entered in the series—500, 600, 700, 800, 900, and 1000—are the different

amounts the Drakes might transfer into their home savings account each month.

Trouble?
If the number 600 was entered in each cell instead of the series, you

probably did not select both cells B4 and C4 before dragging the fill handle.

Repeat Step 5, being sure to select both cells before filling.

◗

6.
In the range A5:A7, enter the values
,
, and
. These monthly values are
36

equal to one year, two years, and three years, respectively. You entered the

years in months because Diane and Glenn plan to deposit money into their home

savings account each month. So, they would make 12 deposits in one year,

they would make 24 deposits in two years, and they would make 36 deposits in

three years.

◗

7.
Format the nonadjacent range B4:G4;A5:A7 with the
Input
cell style.

Next, you’ll enter formulas to calculate the amount of money saved under each plan.

The amount saved is equal to the number of months of savings multiplied by the deposit

per month. You’ll create this table using the same formulas with mixed cell references

discussed earlier in Figure 3-8.

To enter formulas with mixed references to calculate the savings

amounts:

◗

1.
In cell
, enter
=$A5*B$4
. This formula uses mixed references to calculate the
B5

amount of savings generated by saving $500 per month for 12 months. The

calculated value 6000 is displayed in the cell.

◗

2.
Select cell
and drag the fill handle over the range
C5:G5
to calculate total
B5

ings over 12 months for deposits of $500 to $1,000 per month.

◗

3.
With B5:G5 still selected, drag the fill handle down to
to apply the formula to
G7

the remaining cells in the multiplication table.

◗

4.
Format the values in the range B5:G7 using a thousands separator with no digits

to the right of the decimal point, and add a border around each of the cells in

the range.

◗

5.
Click cell
. Figure 3-27 shows the completed and formatted values.
B5

Figure 3-27

Savings plan table

formula uses mixed cell references

to multiply each column value by

each row value

total amount

saved over 12, 24,

and 36 months