Table of Contents
The formula to calculate the percentile rank in Google Sheets is PERCENTRANK.INC. This function takes two arguments, the range of cells and the value within the range to be evaluated, and returns a number between 0 and 1 which expresses the percentile rank of the given value within the range of values. This value equals the percentage of values in the given range which are less than the given value.
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.
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 .