Excel: Substituting Defined Names for Array Formulas

 Last reviewed: November 30, 1994
Article 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

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

  2. In the Name box, type "test" (without the quotation marks).

  3. In the Refers To box, type: 

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

  4. 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

KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.1 2.10 2.2 2.20 3.0 3.00 4.0 4.00 5.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.