Calculating percentile on array with >65536 elements

  • Thread starter Thread starter pinkfloydfan
  • Start date Start date
P

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
 
Unfortunately not...you cannot calculate a percentile on an array with
more than 1 dimension...
 
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...
|
 
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
 
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
 
Back
Top