XL98: Results Do Not Change After Toggling TFE

 Last reviewed: February 3, 1998
Article ID: Q180374
The information in this article applies to:
  • Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In Microsoft Excel 98 Macintosh Edition, the results of the following functions do not change when you select or clear the Transition Formula Evaluation check box: 

   AVERAGE
   MIN
   MAX
   STDEV
   STDEVP
   VAR
   VARP
   COUNT
In earlier versions of Microsoft Excel, the results of the functions may change when you select or clear the Transition Formula Evaluation check box (click Preferences on the Tools menu and click the Transition tab).

CAUSE

The calculation behavior of these functions has changed in Microsoft Excel 98 Macintosh Edition. These functions ignore text values and logical values that are arguments even when the Transition Formula Evaluation check box is selected.

When the Transition Formula Evaluation check box is selected in earlier versions of Microsoft Excel, a text value that is an argument is treated as zero, FALSE is treated as 0 (zero), and TRUE is treated as 1 (one) for these functions.

RESOLUTION

If you want Microsoft Excel 98 Macintosh Edition to treat text values that are arguments as zeros for the listed functions, use the appropriate corresponding "A" function. Several new functions are available in Microsoft Excel 98 Macintosh Edition.

STATUS

This behavior is by design of Microsoft Excel 98 Macintosh Edition.

MORE INFORMATION

Microsoft Excel calculates formulas differently from Lotus 1-2-3. To calculate formulas according to Lotus 1-2-3 rules, follow these steps:

  1. On the Tools menu, click Preferences.

  2. In the Options dialog box, click the Transition tab.

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

In Lotus 1-2-3, a cell that contains text is 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. However, worksheet functions in Microsoft Excel 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 98 Macintosh Edition 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 in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q156445
   TITLE     : 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 button in Microsoft Excel Help, type the following text 

   Lotus 1-2-3, evaluation method
click Show Topics, and then double-click the topic "Calculate formulas by using Lotus 1-2-3 evaluation methods".

Additional query words: XL98 tfe toggle turn on and off
Keywords : xlformula xlui kbualink97 kbui kbusage
Version : MACINTOSH:98
Platform : MACINTOSH

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 3, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.