Microsoft Office Tutorials and References
In Depth Information
If you use MonthNames as an argument for a function, implicit intersection applies only if the function argu-
ment is interpreted as a single value. For example, if you enter this formula in cell E6, implicit intersection
works, and the formula returns 5 (the number of characters in “April”):
=LEN(MonthNames)
But if you enter this formula, implicit intersection does not apply, and the formula returns 12, the number of
cells in the MonthNames range:
=COUNTA(MonthNames)
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-11. For
example, this formula returns the sum of the values in the nine-cell range that extends from Arizona Jan (cell
B2) through Idaho March (cell D4):
=SUM((Arizona Jan):(Idaho Mar))
Referencing a single cell in a multicell named range
You can use Excel's INDEX function to return a single cell from a multicell named range. Assume that range
A1:A10 is named DataRange. The following formula displays the fourth value (the value in A4) in DataRange:
=INDEX(DataRange,4)
The second and third arguments for the INDEX function are optional — although at least one of them must al-
ways 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 (for example, A1:J1), use a formula like the following
one to return the fourth element in the range. This formula omits the second argument for the INDEX function,
but uses the third argument that specifies the column offset with the DataRange range:
=INDEX(DataRange,,4)
If the range consists of multiple rows and columns, use both the second and third arguments for the INDEX
function. For example, if DataRange is defined as A1:J10, this formula returns the value in the fourth row and
fifth column of the named range :
=INDEX(DataRange,4,5)
Applying names to existing formulas
When you create a name for a cell or range, Excel does not scan your formulas automatically and replace the
cell references with your new name. You can, however, tell Excel to “apply” names to a range of formulas.
Select the range that contains the formulas that you want to modify so they will use names rather than cell refer-
ences. Then choose Formulas Defined Names Define Name Apply Names. The Apply Names dialog box ap-
Search JabSto ::




Custom Search