Hi,
Microsoft Excel Transition Options
Some Excel spreadsheet operations — such as calculating formulas, using the keyboard, and entering dates — work differently from those in other spreadsheet applications. However, Excel lets you decide how you want these features to work. You can select either the standard Excel operation or the operation that matches Lotus 1-2-3 and other Lotus 1-2-3-compatible spreadsheet applications.
Transition Formula Evaluation
Excel and Lotus 1-2-3 evaluate certain formulas and expressions differently. Transition formula evaluation allows Excel to calculate formulas and database criteria according to Lotus 1-2-3 rules.
To use Lotus 1-2-3 rules to calculate formulas and database criteriaOn the Tools menu, click Options (Windows) or Preferences (Macintosh), and then click the Transition tab.
Under Sheet Options, select the Transition formula evaluation check box.
The following table compares expressions that are evaluated differently in Excel and Lotus 1-2-3.
This expression |
Is evaluated this way in Lotus 1-2-3 |
And this way in Excel |
|---|---|---|
Cells that contain text |
When the cell is used in a formula, it is given a value of zero (0). |
The cell containing text is ignored in the calculation. In Excel, you cannot combine text and numeric entries in the same formula. |
Boolean expressions |
Boolean expressions are evaluated to 0 or 1 and display 0 or 1 in the cell. For example, 2<3 shows a 1 in the cell to represent True. |
Excel also calculates Boolean expressions as 0 and 1, but displays FALSE or TRUE, respectively, in the cell. |
Note also the following differences:
-
In Excel, database criteria ranges are evaluated differently when you are extracting data, finding data, and using database functions.
For example, computed criteria can use existing field names.
-
Certain Lotus 1-2-3 functions, including @MOD, @VLOOKUP, and @HLOOKUP, are evaluated differently than the equivalent Excel functions.
For example, the Lotus 1-2-3 @VLOOKUP function performs literal matches on text, whereas the Excel VLOOKUP function returns a lookup value for nonliteral text, using the nearest entry in alphabetic order.
When you open a Lotus 1-2-3 worksheet in Excel, transition formula evaluation is automatically turned on for that sheet. This condition ensures that the formulas are calculated according to the preceding Lotus 1-2-3 rules.
If you save the Lotus 1-2-3 worksheet as an Excel workbook, the transition formula evaluation option remains turned on until you turn it off. For workbooks created in Excel, however, transition formula evaluation is not automatically turned on.
Note Avoid turning transition formula evaluation on and off while working with a document in Excel; otherwise, the values calculated on your worksheet might change. If you leave transition formula evaluation turned off, your worksheet adheres to Excel rules. If you leave the option turned on, your worksheet adheres to Lotus 1-2-3 rules.