XL2000: Inconsistent Results Using Natural Language Formulas

When you add data to your worksheet, some formulas may not be recalculated. Also, formulas that appear to be identical may calculate different results.

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.

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:

1. On the Tools menu, click Options.
2. Click the Calculation tab.
3. Click to clear the Accept labels in formulas check box. Click OK. 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:

1. Select a cell that contains a Natural Language Formula.
2. 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:

1. On the Edit menu, click Replace.
2. Type = in the Find what box.
3. Type = in the Replace with box.
4. Click Replace All.

NOTE: This will force all formulas in the active worksheet and dependent formulas in other worksheets to recalculate.

Example of the Behavior

To illustrate this behavior, follow these steps:

1. On the Tools menu, click Options. On the Calculation tab, click to select the Accept labels in formulas check box. Click OK.
2. Type the following values into a new worksheet:

         A1: Units Sold
         A2: 35
         A3: 43
         A4: 28
   					

3. Type the following formula into cell C2, and then press ENTER:
   =SUM('Units Sold')
   Note that the formula returns a value of 106.
4. Type the following values into the worksheet:
   A5: 63
   A6: 54
   A7: 27
   Note that the formula in cell C2 does not update.
5. 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:

For more information about Natural Language Formulas, click Microsoft Excel Help on the Help menu, type Natural Language Formulas in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.