Microsoft Office Tutorials and References

In Depth Information

**Using cell references in formulas**

Figure 12-6
This loan payment table uses formulas that contain mixed references.

You’ll find the Loan.xlsx file with the other examples on the companion website.

The first cell reference, $B6, indicates that we always want to refer to the values in column

B but the row reference (Rate) can change. Similarly, the mixed reference, C$5, indicates we

always want to refer to the values in row 5 but the column reference (Loan Amount) can

change. For example, cell E8 contains the formula = –PMT($B8,$C$3,E$5). Without mixed

references, we would have to edit the formulas manually in each of the cells in the range

C6:F10.

TROUBLESHOOTING

Inserted cells are not included in formulas

If you have a SUM formula at the bottom of a row of numbers and then insert new

rows between the numbers and the formula, the range reference in the SUM function

doesn’t include the new cells. Unfortunately, you can’t do much about this. This is an

age-old worksheet problem, but Excel attempts to correct it for you automatically.

Although the range reference in the SUM formula does not change when you insert

the new rows, it adjusts as you type new values in the inserted cells. The only caveat is

that you must enter the new values one at a time, starting with the cell directly below

the column of numbers. If you enter values in the middle of a group of newly inserted

rows or columns, the range reference remains unaffected. For more information about

the SUM function, see “Using the SUM function” in Chapter 14, “Everyday functions.”