The PERCENTILE function in Google Sheets is useful if you want to know a certain value in a specified range. This value is equal to a specified percentage of the maximum value of the range selected in the spreadsheet.
PERCENTILE function calculates the value at which a certain percentage of the values fall below.
Table of Contents
So, if we have several numerical values and want to know which number among them is greater than X%, we use the
This function is useful when you want to distinguish the top values you have from the lower ones.
Let’s take an example.
Let’s say we have five students in a class; they scored different scores out of 10 in their math exam. Student A scored 4, B scored 5, C scored 6, D scored 7, E scored 8.
We want to know which student’s score was more than half of the other students’ scores. So, we use the
PERCENTILE function in Google Sheets, and the answer would be 6, because it’s the score that’s exactly in the middle, as half (50%). In this case, it’s called the 50th percentile value, which is also known statistically as the median. What about the 25th percentile is there a statistical name for it? The answer is yes, it’s called the first quartile, and the second quartile is the 75th percentile.
Now is this
PERCENTILE function only fixated on getting the middle number of a variety of numerical values? Of course not. You can simply specify the position of the value you want to identify. This value would be between the lowest numerical value and the highest numerical value in the range you selected. Let’s explore how we can do that in the following section.
The Anatomy of the PERCENTILE Function in Google Sheets
So the syntax (the way we write) the
PERCENTILE function is as follows:
Let’s dissect this thing and understand what each of these terms means:
- = (the equal sign) is just how we start any function in Google Sheets.
PERCENTILEis the name of the function we are using.
- () These parentheses are used to host the two values we put in our function, and a comma “,” must separate these values.
Note that the values hosted in any google sheets function are called arguments.
- (data) The first argument of the function is the argument that takes the range of cells we want to explore, whether this range is 1-dimensional or 2-dimensional.
- (percentile) The function’s second argument takes values from 0 to 1, or from 0% to 100%; otherwise, the function will result in an error.
Also, note that if any of these two arguments weren’t specified, the function would result in an error.
Now for further explanation, let’s go through the
PERCENTILE function together, and you will understand it once you start practicing applying it, which we will be doing next.
A Real Example of Using PERCENTILE Function
Take a look at the example below to see how the
PERCENTILE function is used in Google Sheets.
As you can see in the table, the Formula column has the formula of the
PERCENTILE Function, and the Results column has the values resulting in each corresponding formula.
Now let’s look closely at some of the formulas we have in the table.
In this formula, we want the 0th percentile of our selected range (B3:B17), which is the lowest value we have. 32%.
Here, we want the 10th percentile of our selected range (B3:B17), 32.4%. This means that 10% of the values we have are lower than 32.4%
Note: The function’s second argument can be written as a decimal (0.1) or as a percentage (10%) as you like.
And here we want to identify the 25th percentile value of our selected range (B3:B17), 34.5%. Which means that quarter of the values we have are lower than 34.5%
You may make a copy of the spreadsheet using the link I have attached below.
Now that we perfectly understand the concept of the
PERCENTILE function, let’s begin writing our
PERCENTILE function in Google Sheets.
How to Use PERCENTILE Function in Google Sheets
- Simply click on any cell to make it the active cell. For this guide, I will be selecting C3 where I want to show my formula.
- Then, select the range you need the percentile calculations for.
- Then type comma “,”.
- Now type down your percentile value that ranges from 0 (0%) to 1 (100%).
- Finally, close the parentheses and press Enter, and your result will take over.
Note: Your range doesn’t have to be only the values of one column.
Frequently Asked Questions (FAQ)
How is percentile calculated?
Percentile = (Number of Values Below “x” / Total Number of Values) × 100
What is the difference between PERCENTILE.INC and PERCENTILE.EXC?
The difference is that
PERCENTILE.INC can include 0 and 1 in the second argument. While
PERCENTILE.EXC must exclude 0 and 1. Otherwise you will get an error.
How do you interpret percentile rank?
Percentile ranks are often expressed as a number between 1 and 99, with 50 being the average. So if a student scored a percentile rank of 87, it would mean that they performed better than 87% of the other students in his norm group.
That’s pretty much it. Congrats! You can now use the
PERCENTILE function in Google Sheets. Now there are many other Google Sheets functions that you can master. I hope you find it thrilling to learn more and more functions every day.