Microsoft Office Tutorials and References

In Depth Information

**Working with Range and Cell Names**

By the way, implicit intersections are not limited to named ranges. In the preceding example, you

get the same result if cell D5 contains the following formula (which doesn’t use a named range):

=$B$2:$B$8

If you use
MyData
as an argument for a function, implicit intersection applies only if the function

argument consists of a single value. For example, if you enter this formula in cell D3, implicit

intersection works, and the formula returns 3:

=POWER(3,MyData)

But if you enter this formula, implicit intersection does not apply, and the formula returns the

sum of all values in the
MyData
range:

=SUM(MyData)

Using the range operator with names

You can also use the range operator, which is a colon (:), to work with named ranges. Refer to

Figure 3-10. For example, this formula returns the sum of the values for North through West for

January through March (nine cells):

=SUM((North January):(West March))

Referencing a single cell in a multicell named range

You can use Excel’s INDEX function to return a single cell from a multicell range. Assume that

range A1:A50 is named
DataRange.
The following formula displays the second value (the value in

A2) in
DataRange:

=INDEX(DataRange,2)

The second and third arguments for the INDEX function are optional although at least one of

them must always be specified. The second argument (used in the preceding formula) specifies

the row offset within the
DataRange
range.

If
DataRange
consists of multiple cells in a single row, use a formula like the following one. This

formula omits the second argument for the INDEX function, but uses the third argument that

specifies the column offset with the
DataRange
range: