HOW TO CALCULATE PERCENTILE IN EXCEL?

Table of Contents

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.

Excel 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 ]

In this article, we’ll learn the way to calculate percentile in Excel very easily.

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 distrubution 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. ]

121416181
222426282
323436383
424446484
525456585
626466686
727476787
828486888
929496989
1030507090
1131517191
1232527292
1333537393
1434547494
1535557595
1636567696
1737577797
1838587898
1939597999
20406080100
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 EXCEL

We can calculate percentile easily in Excel 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 EXCEL?

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

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

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 excel) of percentile, percentile.inc or percentile.exc functions in excel 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.

EXAMPLES SHOWING THE CALCULATION OF PERCENTILE IN EXCEL

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.

121416181
222426282
323436383
424446484
525456585
626466686
727476787
828486888
929496989
1030507090
1131517191
1232527292
1333537393
1434547494
1535557595
1636567696
1737577797
1838587898
1939597999
20406080100
DATA


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

FOLLOW THE STEPS TO FIND OUT THE 75th PERCENTILE IN EXCEL:

  • 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 1 RESULTS



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 EXCEL:

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



EXAMPLE 2 RESULTS

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

FAQs

IS PERCENTILE FUNCTION AVAILABLE IN EXCEL 365 or EXCEL 2019,2016,2013?

Yes. It is available but just for the sake of backward compatibility.

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)