In this Article

*This tutorial will demonstrate how to calculate “percentile if”, retrieving the k ^{th} percentile in a rage of values with criteria.*

## PERCENTILE Function

The PERCENTILE Function is used to calculate the k^{th} percentile of values in a range where *k* is the percentile value between 0 and 1 inclusive.

=PERCENTILE($D$2:$D$10,0.75)

However, this takes the percentile of the entire range of values. Instead, to create a “Percentile If”, we will use the PERCENTILE Function along with the IF Function in an array formula.

## PERCENTILE IF

By combining PERCENTILE and IF in an array formula, we can essentially create a “PERCENTLE IF” function that works similar to how the built-in AVERAGEIF function works. Let’s walk through an example.

We have a list of scores achieved by students in two different subjects:

Supposed we are asked to find the 75^{th} percentiles of scores achieved for each subject like so:

To accomplish this, we can nest an IF function with the *subject* as our criteria inside of the PERCENTILE function like so:

=PERCENTILE(IF(<criteria range>=<criteria>, <values range>),<percentile>)

=PERCENTILE(IF($C$2:$C$10=$F3,$D$2:$D$10),0.75)

When using Excel 2019 and earlier, you must enter the array formula by pressing **CTRL + SHIFT + ENTER** (instead of **ENTER**), telling Excel that the formula in an array formula. You’ll know it’s an array formula by the curly brackets that appear around the formula (see top image).

**How does the formula work?**

The If Function evaluates each cell in our criteria range as TRUE or FALSE, creating two arrays:

=PERCENTILE(IF({TRUE; FALSE;FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0.99; 0.8; 0.93; 0.42; 0.87; 0.63; 0.71; 0.58; 0.73}), 0.75)

Next, the IF Function creates a single array, replaceing each value with FALSE if its condition is not met.

=PERCENTILE({0.81;FALSE;FALSE;0.42;FALSE;0.63;FALSE;0.58;FALSE}, 0.75)

Now the PERCENTILE Function skips the FALSE values and calculates the 75^{th} percentile of the remaining values (0.72 is the 75^{th} percentile).

**PERCENTILE IF with Multiple Criteria**

To calculate PERCENTILE IF with multiple criteria (similar to how the built-in AVERAGEIFS function works), you can simply multiply the criteria together:

=PERCENTILE(IF((<criteria1 range>=<criteria1>)*(<criteria2 range>=<criteria2>),<values range>),<percentile>)

=PERCENTILE(IF(($D$2:$D$10=$H2)*($C$2:$C$10=$G2),$E$2:$E$10),0.75)

Another way to include multiple criteria is to nest more IF statements within the formula.

**Tips and tricks:**

- Where possible, always reference the position (k) from a helper cell and lock reference (F4) as this will make auto-filling formulas easier.
- If you are using Excel 2019 or newer, you may enter the formula without CTRL + SHIFT + ENTER.
- To retrieve the names of students that achieved the top marks, combine with this with INDEX MATCH.

**PERCENTILE IF** in Google Sheets

The PERCENTILE IF Function works exactly the same in Google Sheets as in Excel: