A Percentile IF function

[RGulley]

Hi i was running if there was a percentile IF function or a way to run
something similar to an average if function.
Lets say I have a data set like below
a 6
b 1
b 2
b 3
a 4
b 5
a 10
c 12
What I want to achieve is to find the percentile of all data points that are
taged "a".
The average if function for this data looks like =AVERAGEIF(D18,"a",E1:E8)
So ideally I want to be able to have a =PERCENTILEIF(D18,"a",E1:E8),
sorting column d is no appropriare as different filters are constantly
applied to this data set.
Potentially I would want to run a LOOKUP loop of something like that
?

 

[Joe User]

The average if function for this data looks
like =AVERAGEIF(D18,"a",E1:E8)
So ideally I want to be able to have a
=PERCENTILEIF(D18,"a",E1:E8)

Try the following array formula [*]:

=percentile(if(D18="a",E1:E8),50%)

[*] Commit an array formula by pressing ctrl+shift+Enter instead of just
Enter. You should see curly braces around the entire formula in the Formula
Bar, e.g. {=formula}. Note that you cannot type the curly braces yourself;
Excel adds them. If you make a mistake, select the cell and press F2, edit
the formula if necessary, then press ctrl+shift+Enter.


----- original message -----