XL97: Inconsistent Results Using Natural Language Formulas
ID: Q192437
|
The information in this article applies to:
-
Microsoft Excel 97 for Windows
SYMPTOMS
When you add data to your worksheet, some formulas may not be recalculated.
Also, formulas that appear to be identical may calculate different results.
CAUSE
This behavior may occur if you are using Natural Language Formulas. Natural
Language Formulas allow you to refer to a cell range in a table by using
row or column labels as the reference name.
WORKAROUND
To work around this behavior, use one of the following methods.
Method 1: Disable the Ability to Use Natural Language Formulas
To disable Natural Language Formulas, follow these steps:
- On the Tools menu, click Options.
- Click the Calculation tab.
- Click to clear the "Accept labels in formulas" check box. Click Yes when
prompted to confirm this option.
When you clear the "Accept labels in formulas" check box, all references to
labels in your formulas are converted to explicit cell references.
NOTE: The "Accept labels in formulas" option is stored on a file-by-file
basis. Clearing the "Accept labels in formulas" check box in one Excel file
will not disable Natural Language Formulas in all of your Excel files.
Method 2: Force Individual Natural Language Formulas to Update
To force an individual Natural Language Formula to update, follow these
steps:
- Select a cell that contains a Natural Language Formula.
- Press F2, and then press ENTER.
The formula will update to the latest set of values for the labels used in
the formula. Repeat these steps for any other Natural Language Formulas
that have not been updated.
NOTE: Excel does not provide a method to determine whether or not a formula
is a Natural Language Formula.
Method 3: Force Natural Language Formula Updating by Replacing Equal Signs
To force all Natural Language Formulas to update the ranges that they refer
to, follow these steps:
- On the Edit menu, click Replace.
- Type "=" (without the quotation marks) in the Find What box.
- Type "=" (without the quotation marks) in the Replace With box.
- Click Replace All.
NOTE: This will force all formulas in the active worksheet and dependent
formulas in other worksheets to recalculate.
MORE INFORMATION
Example of the Behavior
To illustrate this behavior, follow these steps:
- Type the following values into a new worksheet:
A1: Units Sold
A2: 35
A3: 43
A4: 28
- Type the following formula into cell C2, and then press ENTER:
=SUM('Units Sold')
Note that the formula returns a value of 106.
- Type the following values into the worksheet:
A5: 63
A6: 54
A7: 27
Note that the formula in cell C2 does not update.
- Type the following formula into cell C3, and then press ENTER:
=SUM('Units Sold')
Note that the formula returns a value of 250.
At this point, the formulas in cells C2 and C3 appear to be identical, but
they calculate different values.
The reason for this behavior is that Excel evaluates the range represented
by a label when a Natural Language Formula is entered. Excel does not
re-evaluate the ranges represented in a Natural Language formula when new
data is added to the worksheet. If you were to add new values to cells A8
and A9 in this example, neither formula would be updated.
However, if you insert new cells within the range originally referred to in
a Natural Language formula, the formulas would update as expected. For
example, if you were to insert a new cell at cell A3 in this example, both
formulas would recalculate when you enter a new value into cell A3.
NOTE: If you use "Method 1: Disable the Ability to Use Natural Language
Formulas" in the "More Information" section of this article to work around
this behavior, the formulas that you entered in this example are converted
to the following:
C2: =SUM(A2:A4)
C3: =SUM(A2:A7)
REFERENCES
For more information about Natural Language Formulas, click the Office Assistant, type
labels in formulas, click Search, and then click to view
"About labels and names in formulas."
NOTE: If the Assistant is hidden, click the Office Assistant
button on the Standard toolbar. If Microsoft Help is not installed
on your computer, click the article number below to view the article in the
Microsoft Knowledge Base:
Q120802
Office: How to Add/Remove a Single Office Program or Component
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
Q161881
XL97: How to Use Natural Language Formulas
Additional query words:
XL97 nlf elf recalc
Keywords : kbdta xlformula
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb
Last Reviewed: July 26, 1999