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