XL98: How to Use Natural Language Formulas in MS Excel 98Last reviewed: February 18, 1998Article ID: Q181220 |
The information in this article applies to:
SUMMARYMicrosoft Excel 98 Macintosh Edition introduces natural language formulas, which allow you to refer to a cell range in a table by using row or column labels as the reference name. Natural language formulas use the intersection feature that is available in earlier versions of Microsoft Excel. NOTE: This feature is available by default. To find this feature, click Preferences on the Tools menu and click the Calculation tab. You can turn the feature on or off by selecting or clearing the Accept Labels In Formulas option.
MORE INFORMATIONTo refer to an intersection in earlier versions of Excel, you must know the ranges of cells, or you must define range names for the cells before you refer to the range. If the ranges change, you must also update the formulas and defined names. When you use natural language formulas in Excel 98, it's no longer necessary to create defined names or determine the cell ranges in advance. Excel 98 determines the range based on the row and column labels that you provide in the table on the worksheet. For example, in the following table, the formula returns the value 100 for "Product A" in the "First Quarter":
= Product A First Quarter 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 Rules for LabelsNatural language formulas analyze the row and column headings of all tables in the current worksheet to determine the validity of the labels. In most cases, you can use any string as a label. However, there are some guidelines to follow, including the following:
Stacked Column LabelsTables may contain more than one column or row label heading. You can create natural language formulas that refer to multiple headings. To do this, type a space between each of the labels in the formula. The following sample table and formula use a stacked column label in a formula.
A1: B1: 1995 C1: D1: 1996 E1: A2: B2: North C2: South D2: North E2: South A3: Jan B3: 100 C3: 50 D3: 200 E3: 70 A4: Feb B4: 105 C4: 60 D4: 205 E4: 80 A5: Mar B5: 110 C5: 70 D5: 210 E5: 90The following formula returns the value 105 for the region "North" in the year 1995 for the month of February:
=1995 North FebNOTE: Cells B1:C1 and D1:E1 in the table are merged. To merge cells, select the range and click Merge And Center on the Formatting toolbar.
Error Values#NAME?: The #NAME? error means that Excel cannot determine the label. For example, in the formula "=First Quarter Sales," Excel searches for the label "First Quarter Sales." If no matching label is found, Excel searches for the label "First Quarter." If no label is found, Excel searches defined names. The #NAME? error is returned if all the searches fail to find the label. #NULL!: The #NULL! error means that the label is valid but does not refer to a valid intersection. For example, in the formula "=First Quarter Sales," if the label "'First Quarter'" refers to the range A1:A10, and the label "'Sales'" refers to the range B5:E5, the ranges do not intersect.
REFERENCESFor more information about natural language formulas, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Microsoft Excel Help, type the following text
formula, namesand then click Show Topics. Select the "About labels and names in formulas" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.
|
Additional query words: XL98 8.00 NLF ELF
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |