XL97: Functions in Filled Formulas May Not Be Recalculated (154134)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
This article was previously published under Q154134

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 of this behavior, see Example 2 and Example 3 in the "More Information" section in this article.

RESOLUTION

To resolve this problem, do one of the following:

  1. To correct this problem, obtain Microsoft Excel 97 Service Release 2 (SR-2).

    For additional information about SR-2, please see the following article in the Microsoft Knowledge Base:

    151261 OFF97: How to Obtain and Install MS Office 97 SR-2

  2. If you are unable to obtain SR-2, then 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:

    174868 XL97: How to Obtain the Excel 97 Auto Recalculation Patch

  3. If you are unable to do either of the above, follow these steps to temporarily work around this problem:

    1. On the Edit menu, click Replace.
    2. In the Find What box, type =. In the Replace With box, type =. 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. This problem was corrected in Microsoft Office 97 Service Release 2 (SR-2).

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:

  1. Create a new workbook in Microsoft Excel 97.
  2. Type the following into the workbook:

    A1: 1 B1: 2

  3. Select cell C1. Type =SUM(A1:B1) and press ENTER.
  4. Select A1:C1. Drag the fill handle to C3.
  5. Select Row 3. On the Insert menu, click Rows.
  6. Select A2:C2. Drag the fill handle to C3.
  7. Select B1:B4. Type 5. 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:

  1. Create a new workbook in Microsoft Excel 97.
  2. Select A1:A10. Type 1. Press CTRL+ENTER.
  3. Select B1:B10. Type 2. Press CTRL+ENTER.
  4. Select C1:C10. Type =SUM(A1:B1). Press CTRL+ENTER.
  5. Select C8:I8. Type =$A$17. Press CTRL+ENTER.
  6. Select B1:B10. Type 3. Press CTRL+ENTER.
Notice that all values are calculated correctly except for the value in C10, which is not recalculated.

Example 3

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

  1. Create a new workbook in Microsoft Excel 97.
  2. Type the following into the new workbook:

    A1: 1
    A2: 1
    A3: 1
    A4: =SUM(A1:A3)

  3. Select A1:A4. Drag the fill handle to C4.
  4. Select column C. On the Insert menu, click Columns. Make sure column C is still selected and click Columns on the Insert menu again.
  5. Select B1:B4. Drag the fill handle to D4.
  6. Select A3:E3. Type 2. Press CTRL+ENTER.
Notice that all values are calculated correctly except for the value in E4, 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.
Modification Type:MajorLast Reviewed:10/21/2000
Keywords:kbbug KB154134
©2002 Microsoft Corporation. All rights reserved.