XL2000: Natural Language Formula Returns Zero Value (211875)

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 that you typed.

WORKAROUND

When you use natural language 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.

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, follow these steps:

Click Options on the Tools menu, and then click the Calculation tab.
Click to select the Accept labels in formulas check box, and then click OK.

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).

REFERENCES

For additional information about natural language formulas, click the article number below to view the article in the Microsoft Knowledge Base:

279412 XL2000: How to Use Natural Language Formulas

For more information about labels and names in formulas, click Microsoft Help on the Help menu, type name cells in a workbook in the Office Assistant or the Answer Wizard, and then click Search to view the topic.