Excel: Specifying a Range for a Single-Value Argument
ID: Q81272
|
The information in this article applies to:
-
Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
-
Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0, 5.0
SUMMARY
Under certain circumstances, Excel's functions will accept a range as
an argument where a single value or reference is expected.
If the function is entered as an array (COMMAND+RETURN on the
Macintosh, CTRL+SHIFT+ENTER in Windows) the function chooses a value
based on its relative position in the argument range.
If the function is not entered as an array, Excel uses the element of
the range in the same row or column as the formula. This only works if
a single column or a single row is used as the argument within a
formula that expects a single value.
MORE INFORMATION
If Excel cannot accept the range, the result is #VALUE!. This occurs
if the formula is entered in a row or column which is outside the
boundaries of the argument range, or if the argument range is
non-linear (not a single row or column).
For example, if =RIGHT($A$1:$A$5,5) is entered anywhere in the third
row of a spreadsheet, it returns the five rightmost characters of
$A$3. If the formula is entered outside the first five rows as a
non-array formula, it will produce #VALUE!.
REFERENCES
"Microsoft Excel User's Guide," for the Macintosh, version 3.0, pages
271-275
Additional query words:
2.1 2.10 2.20 3.0
Keywords :
Version :
Platform :
Issue type :