XL97: Functions in Filled Formulas May Not Be Recalculated

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 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:

      ARTICLE-ID: Q151261
      TITLE     : 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:

      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://officeupdate.microsoft.com/downloadDetails/xl8patch.htm

3. If you are unable to do either of the above, follow these steps to

   temporarily work around this problem:

    a. On the Edit menu, click Replace.

    b. 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. 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)" (without the quotation marks) 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" (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:

1. Create a new workbook in Microsoft Excel 97.

2. Select A1:A10. Type "1" (without the quotation marks). Press CTRL+ENTER.

3. Select B1:B10. Type "2" (without the quotation marks). Press CTRL+ENTER.

4. Select C1:C10. Type "=SUM(A1:B1)" (without the quotation marks). Press

   CTRL+ENTER.

5. Select C8:I8. Type "=$A$17" (without the quotation marks). Press

   CTRL+ENTER.

6. 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.

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" (without the quotation marks). 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.

Additional query words: XL97 recalc auto recalculation wrong function calc

Keywords          : kbdta xlformula kbfaq
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbpending

Last Reviewed: November 7, 1998