Table of Contents
Calculating percentile rank in Google Sheets can be done easily by using the PERCENTRANK function. This function takes a range of data and a specific value as inputs, and returns the percentage rank of that value within the data set. This allows you to quickly determine the relative position of a particular value compared to the rest of the data. Simply enter the function in a cell and specify the data range and value, and the resulting percentage rank will be displayed. This feature is useful for analyzing data and identifying outliers or trends within a dataset. With the help of the PERCENTRANK function, you can easily calculate percentile rank in Google Sheets and gain valuable insights into your data.
Calculate Percentile Rank in Google Sheets
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 .
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How do I calculate percentile rank in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-calculate-percentile-rank-in-google-sheets/
stats writer. "How do I calculate percentile rank in Google Sheets?." PSYCHOLOGICAL SCALES, 25 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-calculate-percentile-rank-in-google-sheets/.
stats writer. "How do I calculate percentile rank in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-calculate-percentile-rank-in-google-sheets/.
stats writer (2024) 'How do I calculate percentile rank in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-calculate-percentile-rank-in-google-sheets/.
[1] stats writer, "How do I calculate percentile rank in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I calculate percentile rank in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.