XL97: NLFs that use Restricted Labels Return #VALUE! Error

• Microsoft Excel 97 for Windows

SYMPTOMS

When you enter a natural language formula in Microsoft Excel 97, the following problems may occur:

• The formula returns a #VALUE! error.

  -and-

• One of the words in the formula is displayed completely in upper case, for example:

        =Alpha DELTA
  

CAUSE

This problem occurs if the natural language formula contains any restricted labels. You can determine whether a label is restricted by using either of the methods on the "More Information" section.

WORKAROUND

To work around these problems, enclose the labels in the natural language formulas in apostrophes ('). The apostrophes force Microsoft Excel 97 to evaluate the words as labels not as functions. As a result, the problems described in this article do not occur.

For example, instead of using the following formula

   =Alpha Delta

   ='Alpha' 'Delta'

STATUS

This behavior is by design of Microsoft Excel 97.

MORE INFORMATION

When you enter a formula in Microsoft Excel 97, the formula is parsed by the program so that Microsoft Excel 97 can evaluate the formula. If part of the formula can be interpreted as a label (for example, when a table using the label exists on the worksheet) or as a function (for example, when an add-in that contains a function with that name is open) Microsoft Excel 97 assumes that the formula should use the function, not the label.

For example, if you enter the following table into a worksheet

               B1: Charlie   C1: Delta
   A2: Alpha   B2: 1         C2: 3
   A3: Bravo   B3: 2         C3: 4

   =Alpha Delta

   =Alpha DELTA

If the add-in that provides the function is not loaded into memory, the formula should function correctly. However, if you load the add-in, the formula may stop functioning. Use the workaround in this article to prevent this problem from occurring.

Determining Whether a Label is Restricted

To determine whether a label is restricted, use either of the following methods.

Method 1:

1. In a new worksheet, enter the following values

         A1:         B1: Charlie   C1: <Label>
         A2: Alpha   B2: 1         C2: 3
         A3: Bravo   B3: 2         C3: 4
   
      where <Label> is the label you want to test.
   
   

2. Enter the following formula into cell A5:

         =Alpha <Label>
   

   If the formula returns 3, the label you entered in C1 is not restricted. If the formula returns a #VALUE! error, and the formula in cell A5 changes to

         =Alpha <LABEL>
   

   the label you entered is restricted.

1. Select an empty cell in a worksheet.

2. On the Standard toolbar, click Paste Function.

3. In the Function Category list, click All.

   Names that appear in the Function Name list in Proper format (only the first letter is upper case) are restricted.

   Complex    Imaginary     Workday
   Convert    Lcm           Yield
   Delta      Multinomial
   Disc       Networkdays
   Duration   Quotient
   Effect     Received