Home Â» How to Perform a Percentile IF Function in Excel

# How to Perform a Percentile IF Function in Excel

You can use the following formula to perform a Percentile IF function in Excel:

```=PERCENTILE(IF(GROUP_RANGE=GROUP, VALUES_RANGE), k)
```

This formula finds the kth percentile of all values that belong to a certain group.

When you type this formula into a cell in Excel, you need to press Ctrl + Shift + Enter since this is an array formula.

The following example shows how to use this function in practice.

## Example: Percentile IF Function in Excel

Suppose we have the following dataset that shows the exam score received by 20 students who belong to either class A or class B:

Now suppose weâ€™d like to find the 90th percentile of the exam scores for each class.

To do so, we can use the =UNIQUE() function to first create a list of the unique class names. Weâ€™ll type the following formula into cell F2:

`=UNIQUE(B2:B21)`

Once we press enter, a list of unique class names will be displayed:

Next, we can use the =PERCENTILE() function to find the 90th percentile of exam scores in each class.

Weâ€™ll type the following formula into cell G2 and press Ctrl + Shift + Enter so Excel knows this is an array formula:

`=PERCENTILE(IF(B2:B21=F2, C2:C21), 0.9)`

Weâ€™ll then copy and paste this formula into the remaining cells in column G:

From the output we can see:

• The value at the 90th percentile of exam scores in class A was 93.2.
• The value at the 90th percentile of exam scores in class B was 89.8.

Note: We chose to calculate the 90th percentile, but you can calculate any percentile youâ€™d like. For example, to calculate the 75th percentile of exam scores for each class you can replace 0.9 with 0.75Â in the formula.