Page 244 - Excel 2007 Bible

16_044039 ch11.qxp 11/21/06 11:04 AM Page 201
Introducing Formulas and Functions
FIGURE 11.17
You can use a range-intersection formula to determine values.
The formula in cell B9 is
=B1:B6 A3:D3
This formula returns 10, the value in cell B3 — that is, the value at the intersection of the two ranges. 11
The intersection operator is one of three reference operators used with ranges. Table 11.4 lists these operators.
TABLE 11.4
Reference Operators for Ranges
Operator What It Does
: (colon) Specifies a range.
, (comma) Specifies the union of two ranges. This operator combines multiple range references into a single
reference.
(space) Specifies the intersection of two ranges. This operator produces cells that are common to two
ranges.

The real value of knowing about range intersections is apparent when you use names. Examine Figure
11.18, which shows a table of values. I selected the entire table and then used Formulas ➪ Defined
Names ➪ Create From Selection to create names automatically by using the top row and left column.

201