Page 167 - Excel Timesaving Techniques for Dummies

31_574272 ch27.qxd 10/1/04 10:49 PM Page 152
152
Technique 27: Smarter Formula Construction
cell references) — by selecting these cells directly in When selecting a sizeable range of cells for a
the worksheet. formula or the argument of a function, don’t
forget the tricks for quickly selecting larger cell
ranges covered in Technique 10.

Using Labels Instead of Cell

References in Formulas

This final method for building formulas with the
correct cell references doesn’t actually use any cell
references at all. This method enables you to build
formulas for a data table by using row and column
headings that haven’t been assigned as actual range
names in the sheet (as described in Technique 26).

Before you can use this trick of referring to cells in
• Figure 27-7: Entering the arguments for the PMT function formulas by their row and column labels, you have
in the Function Arguments dialog box. to change one of the calculation program options.
Open the Calculation tab of the Options dialog box
If you’ve defined a range name (see Technique (Tools➪Options) and then select the Accept Labels
26) that you want to insert into an argument in Formulas check box.
of a function, select the appropriate argument
text box in the Function Arguments dialog box If you don’t activate the Accept Labels in
and then select the range name to use in the Formulas check box before you create a for-
Paste Name dialog box (Insert➪Name➪Paste). mula that uses row and column labels to refer
to cells, the formula will return the #NAME?
Figure 27-8 shows the worksheet after entering the error value as soon as you complete its entry.
PMT master formula into the first cell of that Loan
Payments table. Thanks to the Insert Function fea- Figure 27-9 illustrates how labels work in formulas
ture, I was able to quickly and easily locate this func- after you activate the Accept Labels in Formula
tion and add the necessary arguments direct from option. Here, you see the beginnings of a simple
the worksheet. table that calculates the total wage due in column D
by multiplying the hours worked in column B by the
hourly rate in column C. To create the formula that
computes Greg’s wage in cell D3, I can now use the
column headings Hours and Rate in place of B3 and
C3, respectively.

In other words, instead of creating in cell D3 the non-
descript formula
=B3*C3

• Figure 27-8: Worksheet after entering the formula with
the PMT function.