XL2000: Tips for Importing Lotus 1-2-3 Files into Microsoft Excel

This article was previously published under Q179841
This article has been archived. It is offered "as is" and will no longer be updated.
Summary
This article contains information about potential issues that you mayencounter when you import Lotus 1-2-3 files into Microsoft Excel.
More information

Opening 1-2-3 Files

Excel can directly open Lotus 1-2-3 files up to version 5.0 (the .wk4 fileformat).

Formatting

When you open a Lotus 1-2-3 worksheet or workbook, Microsoft Excel appliesthe formatting stored in any associated .fmt, .fm3, or .all files. Be surethat the associated formatting file is stored in the same folder as the.wk? file. If you resave a Lotus 1-2-3 file in the Microsoft Excel (.xls)format, Microsoft Excel saves the spreadsheet data and formatting in asingle workbook file.

NOTE: Lotus 1-2-3 .wk4 files do not have an associated formatting file.

Charts

In Lotus 1-2-3, versions 3.x and later, you can create a graph on a chartsheet or create the chart as an object on the worksheet. In Lotus 1-2-3,version 2.x, if you use the WYSIWYG add-in, you can place a graph on aworksheet.

Because Microsoft Excel can read Impress (.fm3) files and Allways(.all) formatting files, you can import a Lotus 1-2-3 worksheet thatcontains a chart on the worksheet. The chart appears on the worksheet as itdoes in Lotus 1-2-3.

Databases

The Database, Criteria, and Extract defined ranges are successfullyimported and function properly. However, database criteria ranges areevaluated differently when you extract data, find data, and use databasefunctions. For example, a criteria of "John" finds only rows with cellsthat contain "John." If you clear the Transition Formula Evaluation check box (click Options on the Tools menu and click the Transition tab), a criteria of "John" finds rows that contain cells with values beginning with "John." For example, cells that contain "John," "Johnson," and "Johnsen" are found.

Calculations

Whenever you open a Lotus 1-2-3 file, the Transition Formula Entry check box is selected. When this feature is selected, Microsoft Excel converts formulas that are entered with Lotus 1-2-3 syntax to Microsoft Excel syntax and makes names defined in Microsoft Excel behave as defined names do in Lotus 1-2-3.

Microsoft Excel calculates formulas differently from Lotus 1-2-3. When acell that contains text is used in a formula, Lotus 1-2-3 assigns a valueof 0 (zero) to the cell. In Microsoft Excel, you cannot combine text andnumeric entries in the same formula. However, when you use a worksheetfunction in Microsoft Excel, a value of 0 is assigned to cells that containtext. For example, if you clear the Transition Formula Evaluation check box, and you type text in cell A1 and the value 100 in cell B1, the formula =A1+B1 returns the #VALUE! error value. However, the worksheet formula =SUM(A1,B1) returns the value 100.

Lotus 1-2-3 evaluates Boolean expressions to 0 or 1 and displays 0 or 1 inthe cell. For example, in Lotus 1-2-3, the expression 2<3 displays 1 in the cell to represent True; Microsoft Excel displays True or False in the cell.If you select the Transition Formula Evaluation check box, Microsoft Excel displays 0 for False and 1 for True.

Some functions, including @MOD, @VLOOKUP, and @HLOOKUP, are evaluateddifferently. For example, the @VLOOKUP function in Lotus 1-2-3 searches foran exact match in the first column; the VLOOKUP worksheet function inMicrosoft Excel assumes the first column is sorted and finds the closestvalue in the first column that does not exceed the lookup value. TheVLOOKUP and HLOOKUP worksheet functions in Microsoft Excel include a fourthargument, range_lookup. If you set this argument to False, Microsoft Excelsearches for an exact match.

To cause Microsoft Excel to calculate formulas as Lotus 1-2-3 does, followthese steps:
  1. On the Tools menu, click Options. Click the Transition tab.
  2. Click to select the Transition Formula Evaluation check box, and click OK.

Calculation Order

Mathematical Order of Precedence Differences:

The following table compares the mathematical operators used by MicrosoftExcel and Lotus 1-2-3.
                     Lotus                 Microsoft   Operator          1-2-3      Precedence   Excel      Precedence   ---------------------------------------------------------------   Exponentiation    ^             1st        ^            2nd   Positive and      + and -       2nd        + and -      1st   negative   Multiplication    * and /       3rd        * and /      3rd   and division   Addition and      + and -       4th        + and -      4th   Subtraction   Comparison        = < >         5th        = < >        5th                     <= >=                    <= >=   Logical NOT       #not#         6th        NOT()        6th   Logical AND       #and# and     7th        AND() and    7th   and OR            #or#                     OR()   String            &             7th        &            7th   concatenation				
NOTE: Lotus 1-2-3 evaluates the exponentiation operator (^) before the negation operator (-). Microsoft Excel evaluates the negation operatorfirst. For example, in Lotus 1-2-3, the formula =-2^4 returns the value-16, but returns 16 in Microsoft Excel. To correct this difference, useparentheses to change the order of evaluation; for example, use =-(2^4)to produce -16.

Links

In Microsoft Excel, when you open a Lotus 1-2-3 .wk4 file that contains alink to another file, the cells may be updated with a #REF! error value. Toupdate an external link in a Lotus 1-2-3 .wk4 file, follow these steps:
  1. In Microsoft Excel, click Links on the Edit menu.
  2. In the Links dialog box, select the link that you want to update. Click Update Now.

    NOTE: If you want to open the source document, click Open. This also updates the external link.
To avoid this behavior, save the file in the Microsoft Excel workbookformat.

Macros

Microsoft Excel 2000 does not run Lotus 1-2-3 macros. You can rewrite anymacros that you need in Microsoft Visual Basic for Applications. Forinformation about writing Excel macro code, see Visual Basic Help.
XL2000 LMI interpreter
Properties

Article ID: 179841 - Last Review: 10/25/2013 22:41:00 - Revision: 4.0

  • Microsoft Excel 2000 Standard Edition
  • kbnosurvey kbarchive kbhowto KB179841
Feedback