XL2000: NLFs That Use Restricted Labels Return #VALUE! Error (212157)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q212157

SYMPTOMS

When you enter a natural language formula in Microsoft Excel 2000, the following unexpected behavior may occur:
  • The formula returns a #VALUE! error.

    -and-

  • One of the words in the formula is displayed in all uppercase letters, for example:

    =Alpha DELTA

CAUSE

This behavior can occur if the natural language formula (NLF) contains any restricted labels. You can determine whether a label is restricted by using either of the methods described in the "More Information" section.

WORKAROUND

To work around these issues, enclose the labels in the natural language formulas in apostrophes ('). The apostrophes force Excel 2000 to evaluate the words as labels and not as functions. As a result, the issues described in the "Symptoms" section do not occur.

For example, instead of using the formula

=Alpha Delta

use the following formula:

='Alpha' 'Delta'

MORE INFORMATION

When you enter a formula in Excel 2000, the formula is parsed by the program so that Excel can evaluate it. 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), Excel assumes that the formula should use the function, not the label.

For example, if you enter the following data into a worksheet 
               B1: Charlie   C1: Delta
   A2: Alpha   B2: 1         C2: 3
   A3: Bravo   B3: 2         C3: 4
and then enter the following formula into cell A5

=Alpha Delta

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

=Alpha DELTA

This issue occurs if the Analysis Toolpak add-in is loaded into random access memory, because the add-in contains a function called "Delta." Excel 2000 assumes that you want the formula to use the Delta function in the add-in and 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 issue 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 that you want to test.

  2. Enter the following formula into cell A5:

    =Alpha Label

    If the formula returns 3, the label that 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 that 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 the proper format (only the first letter is uppercase) are restricted.
The following words are restricted labels: 
   Complex    Imaginary     Workday
   Convert    Lcm           Yield
   Delta      Multinomial
   Disc       Networkdays
   Duration   Quotient
   Effect     Received
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbprb kbusage KB212157
©2003 Microsoft Corporation. All rights reserved.