MS-Excel / Functions and Formula

---

Intersection Operators with Names

Excel's range intersection operator in a single space character. The following formula, for example, displays the sum of the cells at the intersection of two ranges:

=SUM(B1:C20 A8:D8)

The intersection of these two ranges consists of two cells: B8 and C8.

The intersection operator also works with named ranges. A worksheet containing named ranges that correspond to the row and column labels. For example, the name January refers to B2:E2 and the name North refers to B2:B13. The following formula returns the contents of the cell at the intersection of the January range and the North range:

January North

Using a space character to separate two range references or names is known as explicit intersection because you explicitly tell excel to determine the intersection of the ranges. Excel, however, can also perform implicit intersections. An implicit intersection occurs when Excek chooses a value from a multicell range based on the row or column of the formula that contains the reference. A worksheet that contains a range (B3:B8) named NewData. Cell D5 contains the simple formula shown here:

=NewData

Notic that cell D5 displays the value from NewData that corresponds to the formula's row. Similarly, if you enter the same formula into any other cell in rows 3 through 8, the formula displays the corresponding value from NewData. Excel performs an implicit intersection using the NewData range and the rwo that contains the formula. It's as if the following formula is being evaluated:

=NewData 5:5

If you enter the formula in a row not occupied by NewData, the formula returns an error because the implicit intersection returns nothing.

By the way, implicit intersections are not limited to named ranges. In the preceding example, you get the same result if cellD5 contains the following formula (which does not use a name range):

$B$2:$B$B