XL97: Natural Language Formula Returns Zero Value

 Last reviewed: February 12, 1998
Article ID: Q166744
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

When you calculate a formula using column or row labels as a reference in a natural language formula, the formula returns a zero value.

CAUSE

This problem occurs when you enter the formula in the same column or row as the label that you are referencing. Natural language formulas seek out the label you are referencing by first searching to the left, and then searching above the formula you entered.

WORKAROUND

When you use natural label formulas, do not enter formulas in the same column or row as the label you are referencing. Instead, enter the formula in a column to the right of and/or in a row below the label that you are referencing.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

When you use natural language formulas, it is not necessary to create defined names or determine the cell ranges in advance. Microsoft Excel 97 determines the range based on the row and column labels that you provide in the table on the worksheet.

Example

To set up the example, type the following data in a worksheet: 

   A1:              B1: First Quarter  C1: Second Quarter
   A2: Product A    B2: 100            C2: 50
   A3: Product B    B3: 110            C3: 60
   A4: Product C    B4: 120            C4: 70
In this example, the following formula returns the value 150 for the sum of Product A: 

   = Sum('Product A')
However, this formula returns a 0 (zero) if you enter it in column A (for example, in cell A5). For additional information on using natural language formulas, please see the following article in the Microsoft Knowledge Base: 

   Article-ID: Q161881
   TITLE     : XL97: How to Use Natural Language Formulas

REFERENCES

For more information about natural language formulas, click the Index tab in Help, type the following text 

   labels, in formulas
and then double-click the selected text to go to the "Learn about labels and names in formulas" topic.

Additional query words: XL97 NLF
Keywords : xlformula
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 12, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.