XL2000: Natural Language Formula Returns Incorrect Results

ID: Q211754

The information in this article applies to:

Microsoft Excel 2000

SYMPTOMS

When you calculate a formula by using a column or row label as a reference in a natural language formula, the formula returns a total that is incorrect.

CAUSE

This problem occurs when the following conditions are true:

The labels that the natural language formula references are "stacked."

NOTE

The labels are in merged cells.

MORE INFORMATION

Natural language formulas determine the label you reference by first searching to the left, and then searching above the formula you entered. Although a merged cell may appear to encompass many cells, the address of the merged cell is actually the address of the upper-left cell in the merged cell range. The natural language formula uses this upper-left cell as the row or column base for calculations.

The following example contains stacked labels in cells B1 and B2. Each of these cells is merged into column C.


   A1:             B1:     Sales
   A2:             B2:     1996
   A3:             B3: Jan      C3: Feb
   A4: Bill        B4: 100      C4: 110
   A5: Steve       B5: 105      C5: 100

The following formula returns the value 100:


   =Sum(Sales '1996' Jan:Feb Bill)

Because the labels in B1 and B2 are stacked and merged into column C, the natural language formula references the labels in column B and adds only cell B4. However, you might expect that the data in cells B4 and C4 would be added because the formula refers to labels for both Jan and Feb.

REFERENCES

For more information about natural language formulas, click Microsoft Excel Help on the Help menu, type labels, in formulas in the Office Assistant or the Answer Wizard, and then click Search to view the "Learn about labels and names in formulas" topic.

Additional query words: NLF XL2000

Keywords : xlformula
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb