XL98: Error Saving File That Contains COUNT in WK1 Format (175852)

SYMPTOMS

When you save a worksheet in Microsoft Excel 98 Macintosh Edition, you may receive an error message similar to the following:

A formula in a cell (Cell:B5) 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.

You receive this error message even though the worksheet does not contain any functions that are not available in this file format.

When you reopen the file, the formula in the indicated cell is gone. Only the value returned by the formula remains in the cell.

CAUSE

This problem may occur when the following conditions are true:

You are saving the active worksheet in the Lotus 1-2-3 WK1 format. -and-
The worksheet you are saving contains at least one formula that uses the COUNT function.

WORKAROUND

To work around this problem, use either of the following methods:

Before you save the worksheet, replace all references to the COUNT function with references to the COUNTA function. The problem described in this article does not occur when the worksheet contains formulas that use the COUNTA function.

You can quickly convert all of the references by following these steps:
1. On the Edit menu, click Replace.
2. In the Find What box, type COUNT. In the Replace With box, type COUNTA. Click to select the Match Case check box, and then click Replace All.
All references to COUNT are converted to references to COUNTA. -or-
Save the worksheet in the Lotus 1-2-3 WK3 or WK4 format. When you do this, the problem does not occur.

For more information about the COUNT and COUNTA functions, please see the following article in the Microsoft Knowledge Base:

165260 XL97: COUNTA Function Changed to COUNT

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

You can use the COUNT function to count the number of numbers in a range of cells. The COUNTA function allows you to count the number of nonblank cells in a range of cells. For example, if you have the following data

   A1: 1
   A2: 2
   A3:
   A4: x
   A5: y

the formula =COUNT(A1:A5) returns the value 2. This is true because there are two numbers in the range of cells. The formula =COUNTA(A1:A5) returns the value 4 because there are four nonblank cells in the range.

The COUNT and COUNTA functions are supported by all versions of Lotus 1-2-3, so you should not receive an error message when you save a worksheet that contains the COUNT function in the WK1 format.