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

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

Hth,
Merjet

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

"pinkfloydfan" wrote in message oups.com...
|
| 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


"pinkfloydfan" wrote in message
oups.com...
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

Good idea Bernie, thanks