Excel 2010 Bible

Part II: Working with Formulas and Functions

224

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 refer-

ences. 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 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 over-

lapping references in two ranges. Figure 10.17 shows a simple example.

FIGURE 10.17

You can use a range-intersection formula to determine values.

The formula in cell B9 is

=B1:B6 A3:D3

This formula returns 130 , the value in cell B3 — that is, the value at the intersection of the

two ranges.