Thanks, Jerry!
The limitation for using Average() from VBA
is new to me. Maybe VBA internally uses
TRANSPOSE() in this situation??

LeoH

"Jerry W. Lewis" skrev i en meddelelse
...
We are each half right. The array limit is different from the range
limit (number of rows on the worksheet), but it appears that there are
two array limits. For a worksheet array formula like
=AVERAGE(IF(ISERROR(rng),"",rng))
the limit in Excel 2000 is 65535 rows (one less than the number of rows
in the spreadsheet). For calling WorksheetFunction.Average() from VBA,
the limit is 5461 elements, as I experimentally verified before
responding to the OP. I also experimentally verified that the limit
from VBA in XP is 65535.

Jerry

Leo Heuser wrote:

Jerry

I believe, that the maximun array in Excel 97 and on
is limited only by available memory or the worksheet
maximum size 65536 rows x 256 columns. The limit
of 5461 elements is only relevant, when you use the
TRANSPOSE-function.