How to Use PERCENTILE Function in Google Sheets

PERCENTILE Function in Google Sheets

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. 

The PERCENTILE function calculates the value at which a certain percentage of the values fall below. 

So, if we have several numerical values and want to know which number among them is greater than X%, we use the PERCENTILE function.

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:

=PERCENTILE(data, percentile)

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.
  • PERCENTILE is 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.

Percentile Function 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.

=PERCENTILE(B3:B17, 0)

  In this formula, we want the 0th percentile of our selected range (B3:B17), which is the lowest value we have. 32%.

=PERCENTILE(B3:B17, 10%)

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.

=PERCENTILE(B3:B17, 0.25)

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. 

Make a copy of example spreadsheet

 

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

  1. 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.
    Percentile Function in Google Sheets
  2. Then, select the range you need the percentile calculations for.
    Percentile Function in Google Sheets
  3. Then type comma “,”.
  4. Now type down your percentile value that ranges from 0 (0%) to 1 (100%).You can use 90% or 0.9 as a second argument for the function
  5. Finally, close the parentheses and press Enter, and your result will take over.
    percentile formula

      Note: Your range doesn’t have to be only the values of one column.
Selecting a 2-dimintional range for the PERCENTILE function

 

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.

 

Get emails from us about Google Sheets.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like