If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A Percentile IF function
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 ? |
#2
|
|||
|
|||
A Percentile IF function
"RGulley" wrote:
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 ----- "RGulley" wrote: 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 ? |
Thread Tools | |
Display Modes | |
|
|