Calculation differences between Microsoft Excel and Lotus 1-2-3 formulas
Mathematical operators
The following list compares the mathematical operators used by Microsoft Excel and Lotus 1-2-3.
| Description | Lotus 1-2-3 operator | Microsoft Excel operator |
|---|---|---|
| Exponentiation | ^ | ^ |
| Positive and negative | + and – | – (positive is assumed in Microsoft Excel) |
| Multiplication and division | * and / | * and / |
| Addition and subtraction | + and – | + and – |
| Comparison | = < > <= >= <> | = < > <= >= <> |
| Logical | #not#, #and#, and #or# | NOT, AND, and OR worksheet functions |
| Connect two strings of text (concatenation) | & (Release 2.0 or later only) | & |
Order of evaluation The order in which Lotus 1-2-3 performs operations in formulas is as follows: exponentiation, positive and negative, multiplication and division, addition and subtraction, comparison (= < > <= >= <>, #not#, #and#, and #or#), and then concatenation.
Notes
- Lotus 1-2-3 evaluates the exponentiation operator (^) before the negation operator (–); Excel evaluates negation first. For example, the formula =–2^4 produces the value –16 in Lotus 1-2-3, but it produces 16 in Excel. To correct this difference, use parentheses to change the order of evaluation; for example =–(2^4) produces –16.
- Lotus 1-2-3 evaluates comparison operators (= < > <= >= <>) and logical operators (#not#, #and#, and #or#) before the concatenation operator (&); Excel evaluates concatenation before comparison. To correct this difference, use parentheses to change the order of evaluation.
- In both Lotus 1-2-3 and Excel, operators with the same precedence, such as AND and OR, are evaluated left to right.
Calculation rules
Excel calculates formulas and database criteria in a different way than Lotus 1-2-3. To calculate according to Lotus 1-2-3 rules, select the Transition formula evaluation option on the Transition tab (Tools menu, Options command).
Text in calculations In Lotus 1-2-3, cells that contain text are considered to have a value of 0 (zero) when the cell is used in a formula. In Excel, you cannot combine text and numeric entries in a mathematical expression such as addition or subtraction. Worksheet functions in 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.
Boolean values TRUE and FALSE Boolean expressions in Lotus 1-2-3 are evaluated to 0 (zero) or 1 and display 0 or 1 in the cell. Excel allows calculations on the Lotus 1-2-3 values but displays logical values as either TRUE or FALSE (TRUE is equal to 1 and FALSE is equal to 0). For example, in Lotus 1-2-3 the expression 2<3 displays 1 in the cell to represent True; Excel displays the logical value TRUE in the cell.
If the Transition formula evaluation option is selected, Excel displays 0 for FALSE and 1 for TRUE.
Database criteria Database criteria ranges are evaluated differently when you are extracting data, finding data, and using database functions. For example, the criteria "John" will find only rows where the value John is contained within the cell. If you clear the Transition formula evaluation check box, the criteria "John" finds any rows where the value in the cell begins with John
Formula differences
There are also some important differences in how you create formulas in Excel:
- In Excel, you start a formula with an equal sign (=). You can also start a formula with a plus sign (+), as you would in Lotus 1-2-3.
- Although it is not required, you can type an ampersand (@) before Excel functions.
- Excel uses a colon (:) to denote a range of cells, instead of the periods (..) used in Lotus 1-2-3.
Worksheet functions
Functions that are evaluated differently Some functions
The VLOOKUP and HLOOKUP worksheet functions in Excel include a fourth argument, range_lookup, which you can use to have Excel find an exact match. If you set this argument to FALSE, Excel will look for an exact match.
Compatible functions Excel 2000 and later versions contain functions for compatibility with Lotus 1-2-3 Release 4.0 and later. The "A" functions