Returning all data from a list above the nth percentile
Christian,
As you see I do not immediately follow up because of my time zone... I
must admist I had not tested the formula I gave you. Now I tested it
with a dataset in A1:A10. When the dataset was empty it returned #N/A
(ok, this would make sense). WHen I entered data it returned 0. This
did not make sense. I broke the formula as follows, using intermediate
results:
In column B:B:
=PERCENTRANK($A$1:$A$10,A1)
In column C:C
=IF(AND(PERCENTRANK($A$1:$A$10,A1)0.6,PERCENTRANK ($A$1:$A$10,A1)<0.95),A1)
In a separate cell:
=AVERAGE(C1:C10)
This worked. I am still baffled about this and I will probably post a
question myself. Nothing in the documentation says anything about such
behavior. At any rate, if you can afford the luxury of auxiliary
columns you can adopt the approach above.
HTH
Kostis
|