Page 110 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 93
Chapter 6: Copying and Correcting Formulas 93
First time to convert the cell reference from completely relative to completely
absolute (C5 to $C$5)
Second time to convert from completely absolute to a mixed reference with the
row absolute and the column relative ($C$5 to C$5)
Third time to convert from a mixed reference with the row absolute and the
column relative to one with the column absolute and row relative (C$5 to $C5)
Fourth time to convert from a mixed reference with the column absolute and
row relative back to a completely relative reference ($C5 to C5)

You only need to resort to mixed cell references in an original formula when you
intend to copy this formula to empty cells in two directions; that is, both down the
rows and then over the columns to the right or over to columns on the right and then
down the rows, and need to prevent Excel from adjusting either the column letter or
the row number reference in all the copies.

Exercise 6-3 gives you practice in constructing a loan payment table that requires just
this kind of two-dimensional formula copying. To create this payment table, you need
to copy an original formula using the PMT function, the arguments of which require
all the different types of cell references (including both types of mixed cell refer-
ences) in order for all the copies to refer to the correct cells.

Try It

Exercise 6-3: Creating a Loan Table with Mixed Cell References
Open a new workbook in Excel and use its blank Sheet1 worksheet to create this loan
payment table. This table uses the PMT function to compute the monthly mortgage
payments for a sequence of different loan amounts (principals) and interest rates. The
PMT function is a Financial type function that requires three arguments: Rate, which is
the interest rate per payment period, Nper, which is the number or payment periods
for the loan, and Pv, which is the present value or loan amount (the function also
accepts two other optional arguments that you won’t need to use in this exercise).

You begin by entering the table headings and the three initial values (loan amount,
term of the loan, and interest rate) to be used to generate the sequence of the differ-
ent loan amounts and interest rates as well as in the construction of the original PMT
formula that you then copy in two dimensions to the rest of table:

1. Enter the following headings in the designated cells and then widen column A to
suit with AutoFit:
• Loan Payment Table in cell A1
• Initial Principal in cell A2
• Starting Interest Rate in cell A3
• Loan Period in cell A4

2. Enter the following values in the designated cells and format them as indicated:
• 400000 in cell B2 formatted with the Currency Style format with zero deci-
mal places
• 3% in cell B3 formatted with two decimal places
• 30 in cell B4 formatted with the Number format with zero decimal places