You can use the PERCENTRANK function in Google Sheets 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 Google Sheets:
- 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 Google Sheets
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.
And so on.
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 Google Sheets here.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
How to Calculate Cumulative Percentage in Google Sheets
How to Calculate a Weighted Percentage in Google Sheets
How to Use a Percentile IF Formula in Google Sheets
