Microsoft KB Archive/282158

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

Article ID: 282158

Article Last Modified on 2/8/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



This article was previously published under Q282158



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.



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
  Start Excel, and then create the following worksheet: A2: 1    B2:  1  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. 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. Select cells E1:F2. Type the following formula in the formula bar:

=1/A2:B3

 Press CTRL+SHIFT+ENTER to enter the formula as an array.</li>  The array formula returns the following data: <pre class="fixed_text">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. </li></ol>

Example 2
<ol> Repeat the first three steps of Example 1.</li> Select cells E1:F2.</li> Type the following formula in the formula bar:

=1/A1:B2

</li> Press CTRL+SHIFT+ENTER to enter the formula as an array.</li>  The array formula returns the following data: <pre class="fixed_text">E1: #DIV/0! F1: #DIV/0! E2:  1       F2:   1 Notice that cell E1 is flagged, but cell F1 is not flagged. </li></ol>

<div class="moreinformation_section">

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.

Additional query words: XL2002 prb kbnoOfficeAlertID XL2003 XL2007

Keywords: kbpending kbprb KB282158

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.