XL97: Results Unchanged Toggling Transition Formula Evaluation

ID: Q158540

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, the results of the following functions will not change when you toggle the Transition Formula Evaluation option from On to Off, or vice versa:

AVERAGE
MIN
MAX
STDEV
STDEVP
VAR
VARP
COUNT

In earlier versions of Microsoft Excel, the results of the functions can change when the Transition Formula Evaluation option is toggled.

CAUSE

The calculation behavior of the above functions has changed in Microsoft Excel 97. These functions now ignore text values and logical values that are arguments, even when the Transition Formula Evaluation option is enabled. Earlier versions of Microsoft Excel treat text values that are arguments as zero, FALSE as 0 (zero) and TRUE as 1 (one) for these functions when the Transition Formula Evaluation option is enabled.

RESOLUTION

If you would like Microsoft Excel 97 to interpret text values that are arguments for the above functions as zero, use the appropriate corresponding "A" function. Several new functions have been added in Microsoft Excel 97.

STATUS

This behavior is by design of Microsoft Excel 97.

MORE INFORMATION

Microsoft Excel calculates formulas differently from Lotus 1-2-3. To calculate according to Lotus 1-2-3 rules, do the following:

On the Tools menu click Options.

In the Options dialog box, click the Transition tab.

Under Sheet Options, click Transition Formula Evaluation and then click OK.

In Lotus 1-2-3, cells that contain text are considered to have a value of zero (0) when the cell is used in a formula. In Microsoft Excel, you cannot combine text and numeric entries in a mathematical expression such as addition or subtraction. Worksheet functions in Microsoft Excel, however, use the value 0 for cells that contain text. For example, if cell A10 contains text and cell B10 contains the value 100, the formula =A10+B10 returns the error value #VALUE!, if the Transition formula evaluation check box is cleared. However, the formula =SUM(A10,B10) returns the value 100.

Microsoft Excel 97 contains new functions for compatibility with Lotus 1-2- 3 Release 4.0 and later. The new "A" functions AVERAGEA, MAXA, MINA, STDEVA, STDEVPA, VARA, VARPA calculate results by using all of the cells in a range, including blank cells, cells that contain text, and cells that contain the logical values TRUE or FALSE.

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

Q156445 XL97: New A Functions in Microsoft Excel 97

REFERENCES

For more information about switching to Microsoft Excel from Lotus 1-2-3, click the Index tab in Microsoft Excel Help, type the following text

Lotus 1-2-3

and then double-click the selected text to go to the "Switch from Lotus 1- 2-3" topic.

Additional query words: XL98 tfe

Keywords : kbualink97 xlui xlformula
Version : WINDOWS:97
Platform : WINDOWS
Issue type :