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

ID: Q167989

The information in this article applies to:

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

use this formula:

   ='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

and then enter the formula

   =Alpha Delta

you may receive a #VALUE! error and the formula may change to

   =Alpha DELTA

These problems occur if the Analysis Toolpak add-in is loaded into random access memory because the add-in contains a function called Delta. Microsoft Excel 97 assumes that you want the formula to use the Delta function in the add-in not the Delta label in the worksheet.

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.

Method 2:

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.

The following words are restricted labels:

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

Additional query words: XL97

Keywords          : xlformula 
Version           : 97
Platform          : WINDOWS
Issue type        : kbprb

Last Reviewed: November 4, 1998