Microsoft KB Archive/212157

= XL2000: NLFs That Use Restricted Labels Return #VALUE! Error =

Article ID: 212157

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



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:
  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  is the label that you want to test.

</li> Enter the following formula into cell A5:

=Alpha

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

the label that you entered is restricted.</li></ol>

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: <pre class="fixed_text">  Complex    Imaginary     Workday Convert   Lcm           Yield Delta     Multinomial Disc      Networkdays Duration  Quotient Effect    Received

Additional query words: XL2000

Keywords: kbprb kbusage KB212157

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.