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.

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— John, Johnson, and Johnsen.

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— including @MOD, @VLOOKUP, and @HLOOKUP— are evaluated differently. For example, the @VLOOKUP function in Lotus 1-2-3 looks for an exact match in the first column; the VLOOKUP worksheet function in Excel assumes the first column is sorted and finds the closest value in the first column without exceeding the lookup value.

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— AVERAGEA, MAXA, MINA, STDEVA, STDEVPA, VARA, and 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.