Page 151 - Excel Timesaving Techniques for Dummies

28_574272 ch24.qxd 10/1/04 10:47 PM Page 136
136
Technique 24: Efficient Formula Copying
The rate argument cell uses a mixed cell reference reference so that Excel uses the same loan term
where the column is relative and the row is absolute (in the input cell B4) in every copy of the master for-
so that Excel adjusts the rate as you copy the for- mula, regardless of whether this formula is copied
mula across columns B through G but does not down rows 7 through 16 or across columns B
adjust off row 6 when you copy down rows 7 through G.
through 16. Likewise, the pv argument cell (the last
one in the PMT function) uses the opposite mixed Just in case you’re interested, this is how the master
cell reference wherein the column is absolute and formula in cell B7 (er, R7C2) appears in R1C1 notation:
the row relative. This allows Excel to adjust the prin-
=PMT(R6C/12,R4C2*12,RC1)
cipal when you copy the formula down rows 7
through 16 but not adjust off column A when you
If you were to switch to the R1C1 cell notation and
copy across columns B through G.
then move the cell pointer through all the columns
and rows of the table, you would see that every cell
Finally, the nper argument cell (in the second argu-
contains an exact copy of this master formula.
ment in the PMT function) uses an absolute cell