I have created a script in vba that gathers quite a lot of data in an Array. I want to be able to calculate the average and the percentiles of the outcome of the data.

It seems to be possible to write data to the array up to 65536 rows. When I extend the data to 65537 rows I get a type mismatch error.

I am using Excel 2010, and tested that I can calculate an average of 100.000 rows in a spreadsheet. But I cannot write 100.000 rows to an array and calculate the average on the array.

This does not make sense...

Anybody have a way around this (and still being able to calculate average / percentiles of the total data set), preferable without writing all the data to a worksheet?