Return array only containing rows that meet certain conditions

Jadon Latta 0
Viewed 39

I need to determine some percentile groups of a bunch of data I have based on certain groupings, so I need to get an array for the PERCENTILE.EXC function, but only the values where a certain condition is met. I cannot post the actual data, so here is a rough example of the same concept.

If I have this dataset:

enter image description here

I need to now get a separate percentile for each combination of group and gender, so I would have the 90th percentile for 1M, 1F, 2M, 2F. I want to essentially only return the Grade value if the group and gender columns meet the condition, that way I can just make a table as such:

enter image description here

And autofill the formula into the table for all possible combinations. Now, my data is significantly longer, with 10's of thousands of lines, 250+ combinations and not sorted, but the formula should remain the same.

I feel like it should be something like this for the first square in my table (1M)

=PERCENTILE(IF(AND(Group = A3, Gender = B2), Grade),0.9), but I just don't know how to properly do this for a large dataset and would love some guidance.

excel-formula excel
1 Answers

Probably just keep it simple and use a formula like this copied down and across:

=PERCENTILE(IF(($A$2:$A$21=$G3)*($B$2:$B$21=H$2),$C$2:$C$21),0.9)

enter image description here

Note

Percentile is equivalent to percentile.inc. Percentile.exc doesn't work on the above data because you would need at least 9 data points in each group (p/(1-p)) to calculate the 90th percentile and if you did have exactly 9 points in a group it would land on the largest value in the group.

Tom Sharpe0
Related