Part II: Working with Formulas and Functions
FIGURE 9.13
Excel allows you to name 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 abso-
lute references. When you use the pointing technique to create a formula in the Refers
To field 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 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, cell D12 displays
the contents of cell C12 raised to the third power. (C12 is the cell directly to the left of cell
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 deter-
mine the overlapping references in two ranges. Figure 9.14 shows a simple example.
The formula in cell B9 is
=C1:C6 A3:D3
This formula returns 13 , the value in cell C3—that is, the value at the intersection of the
two ranges.
