Excel: Substituting Defined Names for Array Formulas

ID: Q83816

The information in this article applies to:

Microsoft Excel for Windows, versions 2.1, 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0, 5.0

When you create a defined name for a formula, the formula is treated as an array formula when you refer to it (even though the formula is not actually entered as an array).

You can use this feature to avoid entering a formula as an array each time you enter it.

Example

From the Insert menu, choose Name, and then choose Define. (In Microsoft versions 4.0a and earlier, choose Define Name from the Formula menu).

In the Name box, type test.

In the Refers To box, type:

=SUM(IF($A$1:$A$5=1,$B$1:$B$5,0))

Enter the formula "=test" into a worksheet cell and press RETURN.

Note that there are no array brackets "{ }" surrounding the cell formula, yet the cell returns a conditional sum of $B$1:$B$5 based on $A1:$A5 computed as if the SUM(IF()) formula was entered as an array.

NOTE: An array that uses defined names that require more than one cell to be selected must still be entered as an array (such as with the LINEST function).

For more information on the SUM(IF()) array formula, query on the following words in the Microsoft Knowledge Base:


   sum and selectively

REFERENCES

"User's Guide" for the Macintosh, version 3.0, pages 269-281
"User's Guide" for Windows, version 3.0, pages 269-281

Additional query words: 2.1 2.20 3.0

Keywords :
Version :
Platform :
Issue type :