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
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.
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:
- Select an empty cell in a worksheet.
- On the Standard toolbar, click Paste Function.
- 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: kbprb KB167989