Excel 2010 Bible

Chapter 10: Introducing Formulas and Functions

225

The intersection operator is one of three reference operators used with ranges. Table 10.4 lists

these operators.

TABLE 10.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 10.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.

FIGURE 10.18

When you use names, using a range-intersection formula to determine values is even more useful.

Excel created the following names:

North =Sheet1!$B$2:$E$2 Quarter1 =Sheet1!$B$2:$B$5

South =Sheet1!$B$3:$E$3 Quarter2 =Sheet1!$C$2:$C$5

West =Sheet1!$B$4:$E$4 Quarter3 =Sheet1!$D$2:$D$5

East =Sheet1!$B$5:$E$5 Quarter4 =Sheet1!$E$2:$E$5

With these names defined, you can create formulas that are easy to read and use. For example, to

calculate the total for Quarter 4, just use this formula:

=SUM(Quarter4)