XL7: Array Formulas Return #NUM! Error Value

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


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


double-click the selected text, and then double-click "About Worksheet
Functions" to go to the "About Worksheet Functions" topic.
Lastnosti

ID članka: 132221 – Zadnji pregled: 14. feb. 2017 – Revizija: 1

Microsoft Excel 95 Standard Edition

Povratne informacije