Summary

To calculate a conditional percentile, you can use an array formula using the IF function inside the PERCENTILE function. In the example shown, the formula in G5 is:

=PERCENTILE(IF(Table[Gender]=G$4,Table[Score]),$F5)

Where "Table" is an Excel Table with data in B5:D14.

Note: This is an array formula and must be entered with control + shift + enter in Excel 2019 and earlier.

Generic formula

=PERCENTILE(IF(criteria,values),k)

Explanation 

This formula sits inside a small summary table with percentile values in column F and gender values in G4 and H4. Working from the inside out, the IF function is set up like this:

IF(Table[Gender]=G$4,Table[Score])

Here, each value in the gender column is tested against the value in G4, "Male".

The result is an array of boolean values like this:

{88;85;77;FALSE;FALSE;FALSE;83;FALSE;FALSE;79}

Only scores associated with males make it into the array, female scores are translated to FALSE. This array goes into the PERCENTILE function with the k value from F5, 90%.

PERCENTILE automatically ignores FALSE values and returns a result of 86.8.

The reference to Gender in G$4 is locked to prevent the row from changing. The reference to k values, $F5 is locked to prevent the column from changing. As a result, the formula can be copied across the range G5:H7.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.