*61*

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.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

How to Calculate a Five Number Summary in Excel

How to Calculate the Mean and Standard Deviation in Excel

How to Calculate the Interquartile Range (IQR) in Excel