Microsoft KB Archive/178734

= XL98: Natural Language Formula Returns Incorrect Results =

Article ID: 178734

Article Last Modified on 9/11/2002

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q178734



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: Stacked labels are labels that are typed in multiple cells, where one label is in a cell above another label in a cell. -and-


 * The labels are in merged cells.



STATUS
This behavior is by design of Microsoft Excel 98 Macintosh Edition.



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.

For additional information about natural language formulas, please see the following article in the Microsoft Knowledge Base:

161881 XL97: How to Use Natural Language Formulas

Additional query words: NLF ELF

Keywords: kbprb KB178734

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.