Percentile is a Statistical function available in Google Docs Sheets. Find below what is Percentile value and how to use the Percentile Function in Google Sheets.

Don’t skip this function as it’s quite useful and very easy to learn. I have also included how to manually find percentile value in Google Sheets.

I hope that can help you better understand this popular statistical function in Sheets.

Syntax:

PERCENTILE(data, percentile)

There are two arguments in this function and they are “data” and “percentile”.

data – The range containing the dataset (number values) to consider.

percentile – The percentile (l will explain it below) whose value within data will be calculated and returned.

What is Percentile then?

A percentile aka centile is a value below which a ‘given percentage’ of data falls.

Understand how to calculate the percentile in Google Sheets without using the dedicated function first.

How to ‘Manually’ Calculate Percentile in Google Sheets

For example, consider the weight of a group of 9 people. To calculate the percentile ‘manually’ in Sheets, follow the below steps.

Step 1:

Enter the Name and Weight in Kg. as below. The values in the columns must be sorted from smallest to largest. I mean sort column B in ascending order.

Step 2:

The whole values in B2:B10 is considered 100%. What we want to find is the k th percentile and k is no doubt any number between 0 and 100.

Multiply k percent (here I am taking 80%) by the count of values in column B to get the ‘index’ number.

=80%*count(B2:B)

This formula returns 7.2 which is not a whole number.

Step 3:

Round the number 7.2 up to the nearest whole number which is 7. You can use the below formula to round.

=round(80%*count(B2:B))

In cell C2:C10 enter the sequential number starting from 1 to 9 (9 rows). The below formula in cell C2 will auto-generate the serial numbers.

=ArrayFormula(if(B2:B="","",row(B1:B)))

Find the value 7 (index number) in column C or count down the range B2:B until you reach 7th row. The value in that row in column B is the 80th percentile value.

That means 63 is the percentile value and 80% of people are below the weight of “Kathy”.

Note: if you get a whole number in the above step 2, count the rows from B2:B until you reach that number.

Then pick the value and the value in the next cell and find the average. That will be the percentile value.

=average(B8:B9)

Result: 64

Formula Example to the Percentile Function in Google Sheets

There is certain advantages of using the Percentile function in Google Sheets.

1. You can find the percentile value of a large set of data.
2. No need to sort the data.

See the use of Percentile function in Google Sheets. Please refer to the above screenshot and then the formula below.

=PERCENTILE(B2:B11,70%)

This will return the value 63.6, a value between 63 (cell B8) and 65 (cell B9). Albeit the manual method returns 64, that value also comes between this two values. The minor difference is negligible.

The percentile splits the whole data into two portions. One below the percentile value and the other above.

Filter Rows that Contain Values Above and Below the Percentile Value

You can use the function Filter/Query to filter the values below/above the percentile value.

To filter the rows below the Percentile value in Google Sheets, you can use a formula similar to the one below.

=filter(A2:B,B2:B<=PERCENTILE(B2:B,70%))

Here is the formula to filter above the Percentile value.

=filter(A2:B,B2:B>PERCENTILE(B2:B,70%))

Hope you could learn how to use Percentile function in Google Sheets. Enjoy!