View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default 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