Knowledge Base

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

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.
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:

  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.

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:

  1. 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.
  2. On the File menu, click Save As.
  3. In the Save File As Type list, click "WK4 (1-2-3)." In the Save As box, type NLFTest. Then, click Save.
  4. 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.
  5. 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.
  6. On the File menu, click Close, and then click Don't Save.
  7. 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.

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

Keywords: kbprb KB176802


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.