The information in this article applies to:
- Microsoft Excel 98 Macintosh Edition
SYMPTOMS
In Microsoft Excel 98 Macintosh Edition, if you save a workbook in the
Lotus 1-2-3 WK3 or WK4 file format, natural language formulas in the
workbook are converted into values. This is correct behavior and occurs by
design of Microsoft Excel.
However, under certain circumstances, the natural language formulas may be
converted into values that are not correct given the original formula.
Because the formula no longer exists, you may not notice that the value is
incorrect.
CAUSE
This problem occurs when the following conditions are true:
- In the workbook, you create a natural language formula that refers to a
range that contains six or more cells, for example:
A1: Revenue
A2: 129
A3: 130
A4: 131
A5: 132
A6: 133
A7: 134
A8: =SUM(Revenue)
Note that the =SUM(Revenue) formula refers to at least six cells.
-and-
- The range contains at least five different, unique values, for example:
129 130 131 132 133 134
-or-
129 129 131 132 133 134
-and-
- All of the cells in the range contain values with absolute values that
are greater than 128.
For example, the following numbers have absolute values that are greater
then 128:
129 130 200 -129 -130 -200
-and-
- You save the workbook in the Lotus 1-2-3 WK3 or WK4 file format.
When you close and reopen the WK3 or WK4 file, the cells that contain the
natural language formulas contain values that are different from the
original values. For an example that demonstrates this problem, see the
"More Information" section in this article.
NOTE: This problem does not occur when you save a workbook in the Lotus
1-2-3 WK3 or WK4 file format in Microsoft Excel 97 for Windows.
WORKAROUND
To work around this problem, save the workbook in the Microsoft Excel
5.0/95 Workbook format before you save it in the WK3(1-2-3) or WK4(1-2-3)
format. To do this, follow these steps:
- Switch to the workbook.
- On the File menu, click Save As.
- In the "Save File as Type" list, click "Microsoft Excel 5.0/95
Workbook." Change the file name in the Save As box to a new name and
click Save.
- After you save the workbook, click Close on the File menu. If you are
prompted, do not save changes.
- Reopen the workbook you saved in step 3.
- On the File menu, click Save As. In the "Save File as Type" list,
select the appropriate Lotus 1-2-3 file format. Change the file name
in the Save As box if you want. Then, click Save.
When you save the workbook in the Microsoft Excel 5.0/95 Workbook format,
the natural language formulas are converted into normal formulas. Then,
when you save the workbook in the Lotus 1-2-3 file format, the problem does
not occur.
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. We are researching this problem
and will post new information here in the Microsoft Knowledge Base as it
becomes available.
MORE INFORMATION
In Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition,
natural language formulas allow you to make references to a range of cells
without having to define names. For example, if you have a worksheet that
contains the following data
B1: Charlie C1: Echo
A2: Alpha B2: 1 C2: 3
A3: Bravo B3: 2 C3: 4
you can sum the values in the Alpha row by using the following formula
=SUM(Alpha)
and you can find the value at the intersection of Bravo and Echo by using
the following formula:
=Bravo Echo
Example
You can reproduce the problem described in this article by following these
steps:
- In Microsoft Excel 98 Macintosh Edition, create a new workbook. In
Sheet1, enter the following values and formulas:
A1: Revenue1 B1: Revenue2 C1: Revenue3
A2: 129 B2: 128 C2: 129
A3: 130 B3: 129 C3: 129
A4: 131 B4: 130 C4: 129
A5: 132 B5: 131 C5: 129
A6: 133 B6: 132 C6: 129
A7: 134 B7: 133 C7: 129
A8: =SUM(Revenue1) B8: =SUM(Revenue2) C8: =SUM(Revenue3)
Note that the values returned by the formulas are 789, 783, and 774.
- On the File menu, click Save As.
- In the Save File As Type list, click "WK4 (1-2-3)." In the Save
As box, type "NLFTest" (without the quotation marks). Then, click
Save.
- When you receive the following error message
A formula in a cell (Cell:A:A8) could not be converted because it
contains a function that is not available in the file format to
which you are saving. If you continue the save, the formula and
result will be saved, but the function itself will be converted to
an error value.
click No.
- When you receive the following error message
Microsoft Excel cannot convert some of the cells. The total number
of errors found is 3.
click OK.
- On the File menu, click Close, and then click Don't Save.
- Reopen the NLFTest workbook.
Note the following:
- The value in cell A8 is 1013; it should be 789. The problem occurs in
this cell because all of the conditions that are listed in the "Cause"
section are true for the formula in this cell.
- The value in cell B8 is 783, the correct value. The problem does not
occur because the cell range that is referenced by the formula in
cell B8 contains a value that was less than or equal to 128 (cell B2).
- The value in cell C8 is 774, the correct value. The problem does not
occur because the cell range that is referenced by the formula in cell
B9 does not contain at least five different, unique values (all of the
values are 129).
When the value in a cell changes because of this problem, the amount of the
change is always a multiple of 16. In this example, the value 789 was
changed to 1013. The difference is 224, which is a multiple of 16.
|