Table of Contents
The Percentile IF function in Excel is a powerful tool that allows users to calculate the percentile of a specific data range based on a given criteria or condition. This function is especially useful when working with large sets of data, as it allows for the calculation of percentiles for specific subsets of the data. To perform this function, first specify the data range and then provide the criteria or condition that must be met for the percentile calculation. The result will be the percentile value of the data that meets the specified criteria. This function can be accessed through the “Formulas” tab in Excel and is a valuable tool for analyzing and understanding data sets.
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)
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.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How do I perform a Percentile IF function in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-perform-a-percentile-if-function-in-excel/
stats writer. "How do I perform a Percentile IF function in Excel?." PSYCHOLOGICAL SCALES, 30 Apr. 2024, https://scales.arabpsychology.com/stats/how-do-i-perform-a-percentile-if-function-in-excel/.
stats writer. "How do I perform a Percentile IF function in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-perform-a-percentile-if-function-in-excel/.
stats writer (2024) 'How do I perform a Percentile IF function in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-perform-a-percentile-if-function-in-excel/.
[1] stats writer, "How do I perform a Percentile IF function in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, April, 2024.
stats writer. How do I perform a Percentile IF function in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.