Table of Contents
The percentile IF formula in Google Sheets is a useful tool for finding the percentile rank of a specific value within a data set that meets certain criteria. To use this formula, first select the cell where you want the result to appear. Then, enter the formula “=PERCENTILEIF(range, criterion, percentile)” where “range” refers to the data set you want to analyze, “criterion” is the condition that must be met for the value to be included in the calculation, and “percentile” is the desired percentile rank. This formula can help you quickly and accurately analyze your data and make informed decisions based on the results.
Use a Percentile IF Formula in Google Sheets
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 Google Sheets, 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 Google Sheets
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 only among students who were in class A.
To do so, we can type the following formula into cell F2:
=PERCENTILE(IF(B2:B21=E2, C2:C21), 0.9)Once we press Ctrl + Shift + Enter, the 90th percentile of exam scores among students in class A will be shown:
From the output we can see the value at the 90th percentile of exam scores in class A was 93.2.
To calculate the 90th percentile of exam scores for class B, we can simply change the value in cell E2.
The formula will automatically calculated the 90th percentile of exam scores among students in class B:
Note that in these examples 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 Google Sheets:
Cite this article
stats writer (2024). How do I use a percentile IF formula in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-use-a-percentile-if-formula-in-google-sheets/
stats writer. "How do I use a percentile IF formula in Google Sheets?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-use-a-percentile-if-formula-in-google-sheets/.
stats writer. "How do I use a percentile IF formula in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-use-a-percentile-if-formula-in-google-sheets/.
stats writer (2024) 'How do I use a percentile IF formula in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-use-a-percentile-if-formula-in-google-sheets/.
[1] stats writer, "How do I use a percentile IF formula in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I use a percentile IF formula in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.