Page 243 - Excel 2007 Bible

16_044039 ch11.qxp 11/21/06 11:04 AM Page 200
Part II
Working with Formulas and Functions
FIGURE 11.16
Excel lets you give a name to a formula that doesn’t exist in a worksheet cell.
When you use the name MonthlyRate in a formula, it uses the value in B1 divided by 12. Notice that the
cell reference is an absolute reference.
Naming formulas gets more interesting when you use relative references rather than absolute references.
When you use the pointing technique to create a formula in the Refers To box of the New Name dialog box,
Excel always uses absolute cell references — which is unlike its behavior when you create a formula in a
cell.
For example, activate cell B1 on Sheet1 and create the name Cubed for the following formula:
=Sheet1!A1^3
In this example, the relative reference points to the cell to the left of the cell in which the name is used.
Therefore, make certain that cell B1 is the active cell before you open the New Name dialog box; this is very
important. The formula contains a relative reference; when you use this named formula in a worksheet, the
cell reference is always relative to the cell that contains the formula. For example, if you enter =Cubed into
cell D12, then cell D12 displays the contents of cell C12 raised to the third power (C12 is the cell directly
to the left of D12).
Using range intersections
This section describes a concept known as range intersections — individual cells that two ranges have in
common. Excel uses an intersection operator — a space character — to determine the overlapping references
in two ranges. Figure 11.17 shows a simple example.

200