Article ID: 166744
Article Last Modified on 10/22/2000
APPLIES TO
- Microsoft Excel 97 Standard Edition
This article was previously published under Q166744
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 type 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 typed.
WORKAROUND
When you use natural label formulas, do not type formulas in the same column or row as the label that you are referencing. Instead, type 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.
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 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 type 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:
161881 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: kbprb KB166744
