# HOW TO CALCULATE PERCENTILE IN GOOGLE SHEETS?

## INTRODUCTION

There are a number of accounting requirements that are not only used in any specific niche but may be required in day-to-day life.

GOOGLE SHEETS provides us with a variety of ready-to-use functions which can be used anytime to get the desired outcomes. We can also create the formulas and then get the result but it is easier to use the built-in functions that just require the input and give us the output.

One such requirement is the calculation of the PERCENTILE. [ It is not a percent which is calculated on the basis of 100 ]

﻿

## WHAT IS A PERCENTILE ?

A percentile helps us to know the relative standing of the values.

A percentile is a value on a scale of 100 which will show the percent distribution of the data set [ the values given ].

It simply means that if the highest value is 65, all the values are equal to or below it. So the 100th percentile will be 65.

Now, if we want to know the 95TH percentile, it’ll be a value, which will be higher than 95% of the total value population. [ Read this line twice for better understanding. ]

For example,

Let us take a data set of 100 values. In fact, it is a simple count from 1 to 100. [ We’ll take it as the example too later. ]

DATA

Now, the data is pretty simple.

In this data, the 95th percentile will be 95.05 as 95.05 percent values are lower or equal to 95.05.

Similarly, the 60th percentile will be 60.4 as 60% values are lower or equal to 60.4.

﻿

## WHY DO WE NEED TO CALCULATE THE PERCENTILE ?

Percentile is used in the grading of various values which are used in the different fields say the ranking of the students, any examination, checking out the top-performing players on the basis of any ratings, etc.

Many times we need to check the performance relatively. A Percentile is a perfect way to do that.

Percentile doesn’t show the absolute performance but relative only.

For example, suppose a class has 100 students and 50% are the qualifying marks.

It’ll be an absolute measurement when we check the marks of the individual students if they cross the cut-off percent which is 50 in this case or not.

The result is that the number of students crossing 50% can be anything. If the exam is quite tough, it is possible that only 5 students could secure more than 50 percent or if it is easy maybe 60 students secured more than 50 percent. This method is not used practically as we need to get a certain number of students who qualified for the exam. So we use the percentile method.

In the same case, if we want the 50 persons only, we’ll find out the percentile which will provide us a cut-off value or marks above which all the students will qualify.

So, practically, the percentile is a better way for setting the qualifying limits from sample data or values.

﻿

## HOW TO CALCULATE PERCENTILE IN GOOGLE SHEETS

We can calculate percentile easily in GOOGLE SHEETS with a ready-to-use function known as PERCENTILE FUNCTION.

The PERCENTILE FUNCTION is an older version replaced by the new versions PERCENTILE.INC and PERCENTILE.EXC functions. We’ll discuss the new ones.

﻿

## WHAT IS PERCENTILE, PERCENTILE.INC OR PERCENTILE.EXC FUNCTIONS IN GOOGLE SHEETS?

A PERCENTILE, PERCENTILE.INC and PERCENTILE.EXC , all three functions help us to find out the PERCENTILE VALUE in GOOGLE SHEETS.

Percentile function is an older function present for compatibility with the previous versions of the GOOGLE SHEETS.

PERCENTILE, PERCENTILE.INC OR PERCENTILE.EXC , ALL THREE FUNCTIONS FIND OUT THE PERCENTILE IN THE GIVEN DATA SET.

We’ll learn the usage with the help of a few examples given below. But before that let us learn the basics of these functions.

﻿

﻿

## SYNTAX FORMULA OF PERCENTILE FUNCTION﻿

The syntax ( the way a function is phrased in GOOGLE SHEETS) of percentile, percentile.inc or percentile.exc functions in GOOGLE SHEETS are given below.

PERCENTILE FUNCTION:

=PERCENTILE( DATA, Kth percentile )

DATA is the data that can be an array or range on which the calculations will be done.
Kth percentile is the percentile you want to find. Note that the value will be between 0 to 1. [ 95% will be 0.95, 60% will be 0.6] or directly can be put as a percentage.

﻿

PERCENTILE.INC FUNCTION:

=PERCENTILE.INC( DATA, Kth percentile )

DATA is the data that can be an array or range on which the calculations will be done.
Kth percentile is the percentile you want to find. Note that the value will be between 0 to 1. [ 95% will be 0.95, 60% will be 0.6] or directly can be put as a percentage.

﻿

PERCENTILE.EXC FUNCTION:

=PERCENTILE.EXC( DATA, Kth percentile )

DATA is the data that can be an array or range on which the calculations will be done.
Kth percentile is the percentile you want to find. Note that the value will be between 0 to 1. [ 95% will be 0.95, 60% will be 0.6] or directly can be put as a percentage.

﻿

IT IS NOTICEABLE THAT THE ONLY DIFFERENCE IS IN THE NAME OF THE FUNCTION, THE PARAMETERS ARE THE SAME. ( The difference between both the percentile functions .icn and .exc is discussed later in the same article.

﻿

## EXAMPLE 1:

Let us take the same data sample as discussed earlier which contains the values from 1 to 100.

The following table shows the data table.

DATA

Let us find the 75th percentile using all three functions.

FOLLOW THE STEPS TO FIND OUT THE 75th PERCENTILE IN GOOGLE SHEETS:

• Double click the cell where you want the result.
• Enter the function as
• =PERCENTILE( DATA RANGE, 0.75)
• Our data resides from the cell D3 to H22 so our range becomes D3:H22.
• The formula used is
• =PERCENTILE(D3:H22,0.75)
• =PERCENTILE.INC(D3:H22,0.75)
• =PERCENTILE.EXC(D3:H22,0.75)
The result appears as 75.25,75.25, 75.75 respectively.
• The process is shown in the picture below.

﻿

﻿

﻿

## EXAMPLE 2: FIND OUT THE 95th PERCENTILE IN THE GIVEN DATA [ AS PER EXAMPLE 1]

FOLLOW THE STEPS TO FIND OUT THE 95th PERCENTILE IN GOOGLE SHEETS:

• Double click the cell where you want the result.
• Enter the function as
• =PERCENTILE( DATA RANGE, 0.95)
• Our data resides from the cell D3 to H22 so our range becomes D3:H22.
• The formula used is
• =PERCENTILE(D3:H22,0.95)
• =PERCENTILE.INC(D3:H22,0.95)
• =PERCENTILE.EXC(D3:H22,0.95)
The result appears as 95.05,95.05, 95.95 respectively.
• The process is shown in the picture below.

﻿

﻿

﻿

So, this is the way to use the percentile function in GOOGLE SHEETS and the ways to use PERCENTILE, PERCENTILE.INC or PERCENTILE.EXC functions.

## FAQs

### WHAT IS THE NEWER VERSION OF THE PERCENTILE FUNCTION?

PERCENTILE.INC function is the newer version of the older PERCENTILE FUNCTION.

﻿

### WHAT IS THE DIFFERENCE BETWEEN PERCENTILE.INC AND PERCENTILE.EXC FUNCTIONS?

PERCENTILE.INC is the PERCENTILE FUNCTION which includes all the values and the value of the percentile includes both 0 and 1 i.e. inclusive of the edge values of any percentile.

PERCENTILE.EXC is the PERCENTILE FUNCTION which excludes the 0 and 1 values of k i.e. the percentiles [ the minimum and the maximum percentiles ].

PERCENTILE or PERCENTIL.INC are most frequently used under normal circumstances.

﻿

### HOW TO CALCULATE THE MEDIAN FROM THE PERCENTILE FUNCTION?

Median is the central value i.e. any central value in an array of the given data.

Simply make the value of k equal to 0.5.

Use the formula as =PERCENTILE.INC( DATA RANGE, 0.5). The result will give the median value.

﻿

### HOW TO CALCULATE TOP 20 PERCENTILE?

If you want to get the top 20 percentile, it means that you require the top 20 percent of the values. For this you need to get the 80th percentile.

Use the following formula to get the top 20 percentile.

=PERCENTILE.INC(DATA RANGE, 0.8)

## WHAT IS THE DIFFERENCE BETWEEN PERCENT AND PERCENTILE ?

Percent and percentile are totally different and have nothing in common.

Percent simply means PER CENT [ PER 100 ]. A value stated on the rate of per 100.

PERCENT REPRESENTS A MEASURE OF THE PORTION OF A WHOLE WHERE THE WHOLE IS SCALED TO 100. i.e. if we consider the total quantity as 100, how much will be the value in consideration. We express it with a % symbol. [ percent ]

For example, 12 out of 24 is 50% [ if we scale 24 to 100, 12 will be scaled to 50 which will become 50% ]. 6 out of 24 is 25%, and so on.

Simply speaking PERCENTAGE shows us about the proportion. [ It doesn’t indicate the absolute value but a proportion of the whole ].

PERCENTILE IS A VALUE, AGAIN ON A SCALE OF 100 BUT SHOWS THE DISTRIBUTION OF THE VALUES. i.e. it’ll show the number of values below a specific percentile. For example, the 80th percentile is the value below which 80 percent of the values exist in the given data set.

Similarly, top 20 percentile is 80th percentile which will show the top 20% of the values.

It has nothing to do with the percentage but the number of values in any dataset.

﻿

﻿