XL7: Array Formulas Return #NUM! Error Value

The information in this article applies to:

• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, when you use an array greater than 5458 elements in a function, the function may return the #NUM! error value. For example, the following function returns the #NUM! error value:

   A1: =SUMPRODUCT(A2:BT77)

CAUSE

This behavior occurs because in Microsoft Excel, the maximum array size is 5458 elements ( approximately 75 rows by 75 columns). In Microsoft Excel versions 3.0, 4.0, and 5.0, the maximum is 6553 elements.

In Microsoft Excel version 7.0, the maximum array size is still 64K, as it is in earlier versions of Microsoft Excel. However, because Microsoft Excel version 7.0 is a 32-bit application and uses 12-byte data types (instead of 10-byte data types as in the 16-bit versions of Microsoft Excel), the maximum array size in version 7.0 holds fewer elements than the earlier versions of Microsoft Excel.

WORKAROUND

To avoid receiving this error value when you use an array in a function in Microsoft Excel, use an array that contains fewer than 5626 elements.

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

The following is a list of some of the functions in Microsoft Excel that use arrays:

   LINEST()
   MDETERM()
   MINVERSE()
   MMULT()
   SUM(IF())
   SUMPRODUCT()
   TRANSPOSE()
   TREND()

   ARTICLE-ID: Q75376
   TITLE     : Maximum Array Size in Microsoft Excel

REFERENCES

For more information about using arrays as arguments in Microsoft Excel, click the Index tab in Microsoft Excel Help, type the following text

   arrays