Excel: Specifying a Range for a Single-Value Argument

• 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