Microsoft KB Archive/176802

= XL98: Values Incorrect When Natural Language Formulas Are Saved in WK3 or WK4 File Format =

Article ID: 176802

Article Last Modified on 9/11/2002

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q176802



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.</ul>

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.

<div class="workaround_section">

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:


 * 1) Switch to the workbook.
 * 2) On the File menu, click Save As.
 * 3) 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.
 * 4) After you save the workbook, click Close on the File menu. If you are prompted, do not save changes.
 * 5) Reopen the workbook you saved in step 3.
 * 6) 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.

<div class="status_section">

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.

<div class="moreinformation_section">

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 <pre class="fixed_text">              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:

<ol>  In Microsoft Excel 98 Macintosh Edition, create a new workbook. In Sheet1, enter the following values and formulas: <pre class="fixed_text">     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. </li> On the File menu, click Save As.</li> In the Save File As Type list, click "WK4 (1-2-3)." In the Save As box, type NLFTest. Then, click Save.</li> 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.</li> 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.</li> On the File menu, click Close, and then click Don't Save.</li> Reopen the NLFTest workbook.</li></ol>

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.

Additional query words: XL98 english language formulas elf elfs nlf nlfs 256 384 128 768 Converted

Keywords: kbprb KB176802

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.