XL98: How to Use Natural Language Formulas in Microsoft Excel 98

• Microsoft Excel 98 Macintosh Edition

SUMMARY

Microsoft 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 INFORMATION

To 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 Labels

• You can use any letter of the alphabet, a backslash, or an underscore as the starting character.



• You cannot use labels that consist solely of the international decimal point, the plus sign, the minus sign, or the letter "e."



• You cannot use labels that appear to be cell references, for example, you cannot use "FY97."
  
  You can use any string of characters if the string is enclosed in apostrophes (single quotation marks). For example, if you want to use the following string

  =FY97
  
  type the following: ='FY97'




• Excel ignores leading and trailing spaces in labels.
  
  However, spaces are allowed between characters in labels; for example, you can use "Tax Rate."



• You can use a number as a label if the number is between 1,900 and 9,999.



• You can use a date as a label if it uses a built-in date format that contains a day; for example, you can use "12/3/96" or "Dec-3-96."



• You cannot use a label that appears to be a function; for example, you cannot use "Sum()" or "Average()."



• You cannot use a natural language formula in an array formula.



• If Excel finds both a label and a defined name, the defined name takes precedence over the label.
  
  To use a reference as a label, enclose the reference in apostrophes (single quotation marks).
  
  For example, if a defined name called "Sales" exists, and you use the formula "=Sales Software," the formula references the defined name. However, if you type the following formula

  ='Sales' Software

  the range referenced by the row or column label is always used.



• If multiple tables on the same worksheet contain identical labels, the table Excel uses is determined by the location of the cell that contains the formula. In general, the table that is referenced is to the left or above the cell that contains the formula.
  
  For example, if a worksheet contains a table in cells A1:E10 and a table in cells A15:E25, if you type a formula in cell A30, Excel uses the labels in the table in cells A15:E25. However, if you type the same formula in cell A12, Excel uses the first table in cells A1:E10. If Excel cannot determine which table you are referring, it displays the Identify Label dialog box. This dialog box prompts you to select the cell that contains the label you want to use.

Stacked Column Labels

   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: 90 

=1995 North Feb

Error Values

#NAME?:

#NULL!:

REFERENCES

For 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, names

Additional query words: XL98 8.00 NLF ELF

Keywords : kbdta xlformula xllist
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbhowto