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:

Search JabSto ::

Custom Search