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:
- 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:
Q151261 OFF97: How to Obtain and Install MS Office 97 SR-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:
Q174868 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
- If you are unable to do either of the above, follow these steps to
temporarily work around this problem:
- On the Edit menu, click Replace.
- 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:
- 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) 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. 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. Press CTRL+ENTER.
- Select B1:B10. Type 2. Press CTRL+ENTER.
- Select C1:C10. Type =SUM(A1:B1). Press
CTRL+ENTER.
- Select C8:I8. Type =$A$17. Press
CTRL+ENTER.
- 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:
- Create a new workbook in Microsoft Excel 97.
- Type the following into the new workbook:
A1: 1
A2: 1
A3: 1
A4: =SUM(A1:A3)
- Select A1:A4. Drag the fill handle to C4.
- Select column C. On the Insert menu, click Columns. Make sure column C
is still selected and click Columns on the Insert menu again.
- Select B1:B4. Drag the fill handle to D4.
- 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.
Additional query words:
XL97 recalc auto recalculation wrong function calc
Keywords : kbdta xlformula
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbbug
|