Microsoft KB Archive/167989

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

Article ID: 167989

Article Last Modified on 10/22/2000

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q167989





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:

  In a new worksheet, enter the following values <pre class="fixed_text">     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. </li>  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> </li></ol>

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

Additional query words: XL97

Keywords: kbprb KB167989

-

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

© Microsoft Corporation. All rights reserved.