# Working with Lotus 1-2-3 files in Excel

Applies to |
---|

Microsoft 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) - 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:

- Click
**Open**on the**File**menu. - 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. - In the
**Files of type**list, click**Lotus 1-2-3 Files (*.wk?)**. - 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:

- On the
**Tools**menu, click**Options**. - Click the
**Transition**tab. - 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:

- On the
**Tools**menu, click**Options**. - Click the
**Transition**tab. - 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 f****ormula 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:

- On the
**Tools**menu, click**Options**. - Click the
**Transition**tab. - 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

For more information about working with Lotus 1-2-3 files, type **Lotus 1-2-3** in the Office Assistant or on the **Answer Wizard** tab in the Excel Help window, and then click **Search**.