Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

The background error checker does not flag the formula as an error when you enter an array formula in Excel


View products that this article applies to.

This article was previously published under Q282158

↑ Back to the top


Symptoms

When you enter an array formula in Microsoft Excel, 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 or the Cells containing formulars that result in an error check box is selected.

↑ Back to the top


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. In Microsoft Office Excel 2003 and in earlier versions of Excel, click Options on the Tools menu.

    In Microsoft Office Excel 2007, click the Microsoft Office Button, click Excel Options, and then click Formulas.
  3. On the Error Checking tab or under Error Checking, make sure that the Enable background error checking check box is selected. Additionally, make sure that the Evaluates to error value check box or the Cells containing formulars that result in an error 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 data:
    E1:  1        F1:  1
    E2: #DIV/0!   F2: #DIV/0!
    						
    Notice that neither of the returned errors is flagged. A flag is 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 data:
    E1:  #DIV/0!  F1:  #DIV/0!
    E2:   1       F2:   1
    						
    Notice that cell E1 is flagged, but cell F1 is not flagged.

↑ Back to the top


More information

To view the Evaluates to error value check box in Microsoft Excel 2002 and in Excel 2003, click Options on the Tools menu. The Evaluates to error value check box is on the Error Checking tab.

To view the Cells containing formulars that result in an error check box in Excel 2007, click the Microsoft Office Button, and then click Excel Options. The Cells containing formulars that result in an error check box is on the Formulas tab.

↑ Back to the top


Keywords: KB282158, kbprb, kbpending

↑ Back to the top

Article Info
Article ID : 282158
Revision : 4
Created on : 2/8/2007
Published on : 2/8/2007
Exists online : False
Views : 287