Excel: INT() Function Doesn't Translate to WK1 or WK3 Formats

The INT() function will not translate properly between Microsoft Excel version 3.0 and Lotus 1-2-3.

Excel to Lotus

Any cell in an Excel 3.0 document that makes use of the INT() function will result in the "Cannot Write Record" error message when saving to a Lotus 1-2-3 file format (WKS, WK1, WK3).

One possible workaround is to save the file in Excel 2.2 file format, bring the file up in Excel 2.2, and save it out in WKS or WK1 from that version (Excel version 2.2 does not support WK3 file format).

Another workaround is to substitute the TRUNC() function for INT() within the Excel spreadsheet. The TRUNC() function removes the fractional part of a number, and is different from INT() only when handling negative numbers. INT() truncates a number to its lowest integer, while TRUNC() removes the fractional element. INT(-4.3), for example, returns -5, while TRUNC(-4.3) returns -4).

For positive numbers, just substitute TRUNC() for INT(). For either positive or negative numbers The INT() function can be simulated with the formula:

   =IF(OR(number>=0,MOD(number,1)=0),TRUNC(number),TRUNC(number)-1)

Lotus to Excel

Cells that use the INT() function in a Lotus document imported to Excel may appear in varying ways. In some instances, Excel substitutes the TRUNC() function for INT(). In other cases, the formula is entered into Excel in what appears to be a text string. =INT(B1), for example, may appear in Excel as =int(b1).

To convert such a cell into a valid Excel formula, select the cell, click anywhere within the formula as it appears in the formula bar, then press Return.

To convert a large range of such cells to Excel equations:

1. Select the area containing the cells to be converted.

2. Choose Replace from the Formula menu.

3. Type an equals sign (=) in the 'Find What' field.

4. Type an equals sign in the 'Replace With' field.

5. Click 'Replace All'

More Information:

Microsoft is currently researching this problem and will post more information here as it becomes available.

Reference(s):

"Microsoft Excel Function Reference" for the Macintosh, version 3.0, pages 133, 239-40