Follow up percentile question

[fred]

I was reading this question: http://chandoo.org/forums/topic/average-best-95-of-list


and I wonder what if I just want the top 5% percentile instead of the lower 95 percentile data? How should I do it? I personally have never used this function but i think I may be able to apply this to some of my work.

 

[Luke M]

The key part of formula in referenced thread is the PERCENTILE function. This function's arguments are:

PERCENTILE(array,k)

where k is the level of percentile you're looking for, weighted toward the lower values (aka, the 1st percentile out of 100 numbers would give you the smallest value).


So, knowing that, if we want to setup a criteria to get only the top 5%, if would be something like:

=AVERAGE(IF(A1:A100<=PERCENTILE(A1:A100,%5),A1:A100))


In layman's, if the number is less (in value, but "better" in what we want) than the 5% cutoff mark, include it in the average.

 

[fred]

Thanks, Luke!!