Table of Contents
Calculating percentile rank in Excel is a useful tool in determining the relative position of a data point in a given dataset. To calculate percentile rank, first sort the data in ascending order. Then, use the PERCENTRANK function, which takes two arguments: the data range and the specific data point for which you want to find the percentile rank. The function will return a decimal value, which can be converted to a percentage by multiplying it by 100. This percentage represents the percentile rank of the data point in the given dataset. By using this method, you can easily compare the position of a data point to the rest of the data and make informed decisions based on its relative rank.
Calculate Percentile Rank in Excel (With Examples)
You can use the PERCENTRANK function in Excel to calculate the rank of a value in a dataset as a percentage of the total dataset.
This function uses the following basic syntax:
=PERCENTRANK(A2:A16, A2)
This particular example calculates the percentile rank of value A2 within the range A2:A16.
There are also two other percentile rank functions in Excel:
- PERCENTRANK.INC: Calculates the percentile rank of a value, including the smallest and largest values.
- PERCENTRANK.EXC: Calculates the percentile rank of a value, excluding the smallest and largest values.
The following examples show how to use these functions in practice.
Example: Calculate Percentile Rank in Excel
Suppose we have the following dataset that shows the exam scores received by 15 students in a certain class:
Now suppose we would like to calculate the percentile rank of each student’s score.
We can type the following formula into cell B2:
=PERCENTRANK($A$2:$A$16, A2)
We can then copy and paste this formula down to every remaining cell in column B:
Here’s how to interpret each of the percentile rank values:
- The student who scored a 2 ranked at percentile 0 (or 0%) in the class.
- The students who scored a 5 ranked at percentile .071 (or 7.1%) in the class.
- The student who scored a 7 ranked at percentile .214 (or 21.4%) in the class.
Note that when we use the PERCENTRANK function, the smallest value in the dataset will always have a percentile rank of 0 and the largest value in the dataset will always have a percentile rank of 1.
The following screenshot also shows how to use the PERCENTRANK.INC and PERCENTRANK.EXC functions:
There are two things to notice here:
1. The PERCENTILE.INC function returns the exact same values as the PERCENTRANK function.
2. The PERCENTILE.EXC function does not return a value of 0 and 1 for the smallest and largest values in the dataset, respectively.
You can find the complete documentation for the PERCENTRANK function in Excel .
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How do I calculate percentile rank in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-calculate-percentile-rank-in-excel/
stats writer. "How do I calculate percentile rank in Excel?." PSYCHOLOGICAL SCALES, 29 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-calculate-percentile-rank-in-excel/.
stats writer. "How do I calculate percentile rank in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-calculate-percentile-rank-in-excel/.
stats writer (2024) 'How do I calculate percentile rank in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-calculate-percentile-rank-in-excel/.
[1] stats writer, "How do I calculate percentile rank in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I calculate percentile rank in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.