Returning all data from a list above the nth percentile
Christian,
I just saw your message.
So you mean you want the average of those in the top 5%, then the next
35% etc? I think it is the inverse function of PERCENTILE, i.e.
PERCENTRANK that you must use. Given a data set in A1:A10 and a value
in the same scale as the dataset in B1, then
=PERCENTRANK(A1:A10,B1)
will give you the percentage of data with a value below. Thus, to get
the average of those in the 60-95% you would say something like:
=AVERAGE(IF(AND(PERCENTRANK(A1:A10,A1:10)0.6,PERC ENTRANK(A1:A10,A1:A10)<0.95),A1:A10))
Does this help?
Kostis
|