Microsoft KB Archive/132221

= XL7: Array Formulas Return #NUM! Error Value =

PSS ID Number: 132221

Article Last Modified on 3/21/2001

-

The information in this article applies to:


 * Microsoft Excel for Windows 95 7.0

-



This article was previously published under Q132221





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

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

75376 : Maximum Array Size in Microsoft Excel

