XL2002: Array Formula with Error Not Flagged (282158)


The information in this article applies to:

  • Microsoft Excel 2002
This article was previously published under Q282158

SYMPTOMS

When you enter an array formula, the background error checker does not flag the formula as an error, even when one or more of the array elements returns an error.

This behavior occurs even when the Evaluates to error value check box is selected on the Error Checking tab in the Options dialog box. (To view the Options dialog box, click Options on the Tools menu.)

CAUSE

If an error does not occur in the first element of the array formula, the error flag does not appear. When a formula is entered as an array, the Excel error checker is designed to check only the first formula in the array. The following examples illustrate this behavior:

Example 1

  1. Start Excel, and then create the following worksheet: 
    A2:  1    B2:  1
  2. On the Tools menu, click Options.
  3. On the Error Checking tab, make sure that the Enable background error checking check box is selected, and that the Evaluates to error value check box is selected.
  4. Select cells E1:F2.
  5. Type the following formula in the formula bar:

    =1/A2:B3

  6. Press CTRL+SHIFT+ENTER to enter the formula as an array.
  7. The array formula returns the following: 
    E1:  1        F1:  1
E2: #DIV/0!   F2: #DIV/0!
    Note that neither of the returned errors is flagged (a small mark in the upper left corner of the cell).

Example 2

  1. Repeat the first three steps of Example 1.
  2. Select cells E1:F2.
  3. Type the following formula in the formula bar:

    =1/A1:B2

  4. Press CTRL+SHIFT+ENTER to enter the formula as an array.
  5. The array formula returns the following: 
    E1:  #DIV/0!  F1:  #DIV/0!
E2:   1       F2:   1
    Note that cell E1 is flagged, but cell F1 is not flagged.

MORE INFORMATION

For more information about error checking, click Microsoft Excel Help on the Help menu, type about protected workbooks in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:2/21/2002
Keywords:kbprb KB282158
©2002 Microsoft Corporation. All rights reserved.