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

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.

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.

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.