Working with Lotus 1-2-3 Files in Excel 2000 

You can work with files from Lotus 4.0 (*.wk4), Lotus 3.x and Lotus 1-2-3/W (*.wk3.*fm3, *.pic when included in an *.all file), Lotus 2.x (*.wk1, ALL, FMT), and Lotus 1.0 and 1.0A. Following are issues to be aware of, as well as tips for working with Lotus 1-2-3 files in Microsoft Excel.

Opening Lotus 1-2-3 Files

To open a Lotus 1-2-3 file that is one of the formats listed above, follow these steps:

1. Click Open on the File menu.
2. In the Look in list, click the drive or folder that contains the file you want to open, and then locate the folder that contains the file.
3. In the Files of type list, click Lotus 1-2-3 Files (*.wk?).
4. Double-click the file you want to open.

Formula Entry and Evaluation

Excel accepts and calculates formulas differently than Lotus 1-2-3 does, but you can make Excel recognize and evaluate Lotus 1-2-3 style entries. Following are issues to be aware of, and how to get the results you expect in Excel.

Function Entry and Name Evaluation

In Lotus 1-2-3, if you wanted to find the average of a range of numbers, you could type the following in a cell and see the result:

@AVG(A1,B1,C1)

If you tried to type this formula in Excel, you would normally receive an error message such as "That function is not valid." However, you can change Excel so that when you type the formula above, Excel automatically converts it to:

=AVERAGE(A1,B1,C1)

You can also make Excel automatically apply names for ranges as you enter references to ranges in formulas, just like in Lotus 1-2-3.

To change Excel to recognize Lotus 1-2-3 style entries and use defined names in the same way Lotus does, follow these steps:

1. On the Tools menu, click Options.
2. Click the Transition tab.
3. Click Transition formula entry, and click OK.

Formula Evaluation

Excel evaluates formulas differently from Lotus 1-2-3. For example, when a cell that contains text is used in a formula, Lotus 1-2-3 assigns a value of 0 to the text. So a formula such as

="text"+1

would return a value of 1 in Lotus 1-2-3. Normally, Excel would return a #VALUE! error for this formula because it contains both text and numeric entries. Another example is that Lotus 1-2-3 evaluates Boolean expressions to 0 or 1 and displays 0 or 1 in the cell, as opposed to Excel, which displays FALSE or TRUE in the cell. For example, in Lotus 1-2-3, the expression 2<3 evaluates as 1 to represent TRUE; in Excel, the same expression is displayed simply as TRUE.

To change Excel to calculate text and logical values in formulas in the same way that Lotus 1-2-3 does, follow these steps:

1. On the Tools menu, click Options.
2. Click the Transition tab.
3. Click Transition formula evaluation, and click OK.

Order of Operations

Excel and Lotus 1-2-3 evaluate formulas using the same order of calculation, except for the order in which the exponentiation operator (^) and the negation (-) operator are calculated. In Lotus 1-2-3, the exponentiation operator is calculated before the negation operator. To correct this difference, use parentheses to change the order of evaluation; for example, use =-(2^4) to produce -16 in Excel.

Function Calculation

Some Excel functions calculate differently than Lotus 1-2-3. For example, the Average function in Excel calculates the average of a range of numbers; any text or logical values in the range are ignored. In Lotus 1-2-3, the equivalent function calculates the average of a range of numbers, and also includes text (which evaluates to 0) and logical values (which evaluate as 1 for TRUE and 0 for FALSE) in the range. Excel includes a set of functions that evaluate text and logical values as Lotus does for equivalent functions. These functions behave the same way regardless of whether the Transition formula evaluation or Transition formula entry check boxes are selected.

The functions have the same names as the Excel equivalent functions with the letter "A" appended to them:

Function Name	Description
AVERAGEA	returns average value in a range
MINA	returns minimum value in a range
MAXA	returns maximum value in a range
STDEVA	returns the standard deviation of a sample of values
STDEVPA	returns the standard deviation of an entire population of values
VARA	returns the estimated variance of a sample
VARPA	returns the estimated variance of an entire population

Keyboard Shortcut Keys

Lotus 1-2-3 uses different keyboard shortcut keys than Excel 2000. To change Excel to recognize Lotus 1-2-3 shortcut keys, follow these steps:

1. On the Tools menu, click Options.
2. Click the Transition tab.
3. Click Transition navigation keys, and click OK.

Formatting

Lotus 1-2-3 uses add-ins to provide formatting features such as borders, shading, and fonts. When you save your worksheet file in Lotus 1-2-3, a formatting file is saved separately. In Lotus 1-2-3 versions 2.x, you can use the Impress or Allways add-ins. Impress creates files with the same name as your worksheet file plus an .FMT extension. (This add-in is also used in Lotus 1- 2-3 versions 3.x, however, the extension is .FM3.) Allways creates files with the same name as your worksheet file plus an .ALL extension.

When you open a Lotus 1-2-3 worksheet or workbook, Excel applies the formatting stored in the associated *.FMT, *.FM3, or .ALL formatting file when the associated formatting file is stored in the same folder as the .wk? file.

Formatting not supported by Lotus that you apply to the file after opening it in Excel will be lost if you save the file in its original Lotus format. If you resave the file in the Microsoft Excel (.xls) format, however, Excel formatting you applied to the file is retained and is saved with worksheet data in a single workbook file.

Macros

Excel 2000 does not run Lotus 1-2-3 macros. You can rewrite any macros that you need in Microsoft Visual Basic® for Applications. For information about working with Excel macro code, type Visual Basic Help in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

More Information