If you work with numbers and you don’t use Excel, luckily, Google Sheets can provide some great functionality that won’t make you miss a different program.
When you work with numbers, you will have to do data analysis at some point. Like Excel, Google Sheets have functions where you don’t have to do a ton of calculations by hand — the program will do some brilliant analysis for you.
One of these great functions in Google Sheets is called the Percentile function.
Table of Contents
What is the Percentile Function?
The percentile formula provides the value below which a specified percentage of observations within a group falls. When you calculate the percentile of a range of data, you end up splitting your data into two portions. One portion is above the percentile value and the other portion is below it. When it comes to grading curves in exams, the percentile concept is used.
It is pretty useful and easy to learn. However, it should not be confused with percentage. Before we go deeper into the percentile function, let’s look at how it differs from a percentage.
Is This the Same as a Percentage?
No, the percentile is not the same as a percentage. A percentage represents a value out of 100. For example, 50% of 100 is 50, and 50% of 40 is 20. On the other hand, the percentile represents position or rank in data.
Let’s use a test for an example. Normally, if a student scores only 55% on an exam of 100 questions, this would be a failing score. That’s because the student only got slightly more than half of the questions correct. However, if the rest of the class scored only 30% or 40% correct out of 100 questions, then the student that scored 55% on the exam would actually be in the highest percentile on a grading curve. We will see this in action in a later example.
How To Use The Percentile Function
We will go back to using a test score example. Let’s say you are a teacher and it is time to compile the final exam scores. The first thing you would do is create a simple list with the students’ names and scores.
As you can see, our students’ names are in cells A3 to A13 and their corresponding scores are in cells B3 through B13.
Now it is time to use the Google sheets percentile formula:
=PERCENTILE( Data Start : Data End, Percentile )
Let’s start by calculating the 50 % percentile of these test scores.
First, Click on cell C3
For the next step:
- Insert one of these formulas: =PERCENTILE(B3:B13,.5) or =PERCENTILE(B3:B13, 50%)
- Hit Enter
Note: It does not matter if you use the decimal (.5) or percentage numeral (50%) in the percentile section. Google Sheets will still calculate the correct percentile.
As you can see, the number 75 appears in cell C3. This means that anyone with a score of 75 or less, such as Lisa, is in the lower 50th percentile of test scores. However, anyone with 76 or higher, such as Kim, is in the upper 50th percentile of test scores.
Now let’s do this for each 10th percentile in column C.
- First, delete the current formula in C3
- Add this formula to C3: =PERCENTILE(B3:B13,.0)
- Add this formula to C4: =PERCENTILE(B3:B13,.1)
- Add this formula to C5: =PERCENTILE(B3:B13,.2)
- Add this formula to C6: =PERCENTILE(B3:B13,.3)
- Add this formula to C7: =PERCENTILE(B3:B13,.4)
- Add this formula to C8: =PERCENTILE(B3:B13,.5)
- Add this formula to C9: =PERCENTILE(B3:B13,.6)
- Add this formula to C10: =PERCENTILE(B3:B13,.7)
- Add this formula to C11: =PERCENTILE(B3:B13,.8)
- Add this formula to C12: =PERCENTILE(B3:B13,.9)
- Add this formula to C13: =PERCENTILE(B3:B13,1)
Now that you have plugged in all of your percentile formulas from 0% to 100%, your Test Score chart should look like this:
Of course, you can move the “Percentile” column further away from the “Name” and “Score” columns. I only kept them close together for the purpose of this lesson.
When you click on each cell, the formula appears in the top bar. This is useful in case you forget which percentile you are looking at or you need to make adjustments.
Now our chart shows that 98 is in the 100th percentile. This means that Lulu did better than 100% of the rest of the class, but she did not score 100% on the exam. Alex, on the other hand, is on the 0 percentile. With a score of 40, he did better than 0% of his classmates and is in last place.
You should also note the difference between how we reference 10% versus 100%. The 10th percentile is represented by ( .1) in the formula, but when we want the 100th percentile, we simply use a whole one (1).
Conclusion
Well, there we have it! You now understand how to use the percentile Google Sheets function. As you can see, this is a very simple and useful function that you can use when you want to calculate the ranking of specific numbers within a data range.
I hope you found this tutorial helpful and can take more comfort in analyzing future data.
