XL97: Functions in Filled Formulas May Not Be Recalculated

Last reviewed: March 19, 1998
Article ID: Q154134

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, some formulas on a worksheet may not be recalculated automatically. This behavior may occur even if you choose to use automatic calculation for formulas in the worksheet.

CAUSE

This problem may occur in either of the following cases:

Case 1

You create a range of formulas that reference data in other cells.
```
    -and-
```
You insert a row within the range of formulas.
```
    -and-
```
You select the values and the formula in the row beside the new row, and then fill the data into the new row.
```
    -and-
```
You change the values of the cells that the formulas reference.

NOTE: To see an example, see Example 1 in the "More Information" section in this article.

Case 2

You create a range of two or more equivalent formulas that reference data in other cells.
```
    -and-
```
You create a second range of equivalent formulas, and the second range intersects the first range of formulas.
```
    -and-
```
You change values in the cells that the first range of formulas references.

NOTE: To see an example, see Example 2 in the "More Information" section in this article.

RESOLUTION

To resolve this problem, obtain and install the Excel 97 Auto Recalculation Patch.

For information about downloading and installing this patch, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q174868
   TITLE     : XL97: How to Obtain the Excel 97 Auto Recalculation Patch

Or, go to the following Microsoft World Wide Web site:

   http://www.microsoft.com/office/excel/enhancements/xl8p1.asp

To work around this problem, follow these steps:

On the Edit menu, click Replace.
In the Find What box, type "=" (without the quotation marks). In the Replace With box, type "=" (without the quotation marks). Click Replace All.

The formulas are recalculated correctly after the "=" is replaced in the cells.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

To select automatic recalculation, click Options on the Tools menu, click the Calculation tab, and then click Automatic.

Example 1

To see an example of the problem as described for the first case in the "Cause" section, follow these steps:

Create a new workbook in Microsoft Excel 97.
Type the following into the workbook:
```
      A1: 1  B1: 2
```
Select cell C1. Type "=Sum(A1:B1)" (without the quotation marks) and press ENTER.
Select A1:C1. Drag the fill handle to C3.
Select Row 3. On the Insert menu, click Rows.
Select A2:C2. Drag the fill handle to C3.
Select B1:B4. Type "5" (without the quotation marks). Press CTRL+ENTER.

Note that values in C1:C3 are recalculated correctly but that the value in C4 is not.

Example 2

To see an example of the problem as described in the second case in the "Cause" section, follow these steps:

Create a new workbook in Microsoft Excel 97.
Select A1:A10. Type "1" (without the quotation marks). Press CTRL+ENTER.
Select B1:B10. Type "2" (without the quotation marks). Press CTRL+ENTER.
Select C1:C10. Type "=sum(a1:b1)" (without the quotation marks). Press CTRL+ENTER.
Select C8:I8. Type "=$a$17" (without the quotation marks). Press CTRL+ENTER.
Select B1:B10. Type "3" (without the quotation marks). Press CTRL+ENTER.

Notice that all values are calculated correctly except for the value in C10, which is not recalculated.

REFERENCES

For more information about automatic recalculation, click Contents And Index on the Help menu, click the Index tab in Microsoft Excel 97 Help, type the following text:

   recalculating formulas, calculation methods

and then double-click the selected text to go to the "Change the way Microsoft Excel calculates formulas" topic.

Additional query words: XL97 recalc auto recalculation wrong function
Keywords : xlformula kbdta kbfaq
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbbug
Solution Type : kbpending

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: March 19, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.