Calculating percentile on array with >65536 elements

[pinkfloydfan]

Hi all

The Percentile worksheet function is great but unfortunately seems to
have a limit of 65536 elements; try and use it on an array with more
elements than that and it returns an error.

So, if you have a larger array does anyone have any better ideas for
calculating a specific percentile without sorting the array first?

Thanks a lot
Lloyd

 

[merjet]

I made a 4x20,000 array (80,000 elements) and it worked fine for me.

Hth,
Merjet

 

[pinkfloydfan]

Unfortunately not...you cannot calculate a percentile on an array with
more than 1 dimension...

 

[Niek Otten]

According to HELP 8191 elements is the max

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Unfortunately not...you cannot calculate a percentile on an array with
| more than 1 dimension...
|

 

[pinkfloydfan]

That is very true but in actual fact the function works with upto
65536 elements (having tested it out)...just a small example of the
Help being inaccurate.

However, back to the original problem...does anybody have any ideas
how to calculate a specific percentile of a very large array without
having to sort the array first please?

Cheers
Lloyd

 

[Bernie Deitrick]

Lloyd,

You could use Percentile on a statistical sample (random selected values) of the array. Probably
not a big error in doing so, if your numbers are well behaved ;-)

Or just bite the bullet and sort.

HTH,
Bernie
MS Excel MVP

 

[pinkfloydfan]

Good idea Bernie, thanks