XL97: Inconsistent Results Using Natural Language Formulas (192437)


The information in this article applies to:

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

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:
  1. On the Tools menu, click Options.
  2. Click the Calculation tab.
  3. 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:
  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 "=" (without the quotation marks) in the Find What box.
  3. Type "=" (without the quotation marks) 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.

MORE INFORMATION

Example of the Behavior

To illustrate this behavior, follow these steps:
  1. Type the following values into a new worksheet:
          A1: Units Sold
      A2: 35
      A3: 43
      A4: 28
  2. Type the following formula into cell C2, and then press ENTER: 
         =SUM('Units Sold')
    Note that the formula returns a value of 106.
  3. Type the following values into the worksheet: 
          A5: 63
      A6: 54
      A7: 27
    Note that the formula in cell C2 does not update.
  4. 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:

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

161881 XL97: How to Use Natural Language Formulas

Modification Type:MajorLast Reviewed:8/27/2002
Keywords:kbprb KB192437
©2002 Microsoft Corporation. All rights reserved.