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
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.”