How to Use PERCENTILE Function in Excel (With an Example)

Calculating percentile is a too common task in Excel for any business purpose or other institutional purposes to analyze data. You can easily calculate it by using the Excel PERCENTILE function.
Look at the following picture.

The above image is an overview of the use of the PERCENTILE function. In this article, you will learn the use of the PERCENTILE function in Excel with suitable examples and vivid illustrations.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


What is Percentile: A Brief Idea

Percentile is a value on a scale of 100 that indicates the percent of a distribution that is equal to or below it. That means percentile tells us how a value compares to the other values in a given range. If a value is at the kth percentile, means that it is higher than k% of the other values of the group.
For example, If a boy’s height is in the 80th percentile for boys his age that means he is taller than 80% of the other boys of his age. To know more, go through the following write-up.


Introduction to the PERCENTILE Function

The PERCENTILE function is categorized under the Statistical function in Excel. It is used to analyze data.

Purpose of the PERCENTILE Function:

To calculate the kth percentile of the values in a particular data range.

Syntax:

=PERCENTILE(array, k)

Arguments:

Arguments Required/Optional Explanation
array Required The array or range of data for which we’ll find percentile
k Required The percentile value which is between 0 to1

Return Parameter:

Returns kth percentile of a data range.

Available in:

Excel 2003 and upper versions.


How to Use PERCENTILE Function in Excel

To understand the uses of the PERCENTILE function properly, let’s see an example.

For that, we will use the following data in our dataset:

Use PERCENTILE Function in Excel

Now I will find here the 10th percentile. We can find it in three ways with the PERCENTILE function. Let’s see all of them one by one.


Example 1: ‘k’ in Decimal Format

We will use the decimal format for k in this way. Let’s see the following steps.

Steps:

➤ Type the following formula in Cell B13:

=PERCENTILE(B5:B10,0.1)

➤ Press the Enter button.

Use PERCENTILE Function in Excel

Soon after, we get the result like the image below.

Use PERCENTILE Function in Excel


Example 2: ‘k’ in Percentage Format

We can input the value of k in percentage format too. Here, the value k=0.1 in Cell B13 is the same as k=10% (the 10th percentile).

Use PERCENTILE Function in Excel

Let’s see the following steps one by one.

Steps:

➤ If you want to find the 50th percentile then type the following formula in Cell B13 as shown below:

=PERCENTILE(B5:B10,50%)

➤ Press Enter.

Use PERCENTILE Function in Excel

Then we will get the result like the image below.

Use PERCENTILE Function in Excel

Notice the 50th percentile in Cell B13 falls halfway between the values of 13 and 14. So the PERCENTILE function interpolates and produces the result 13.5.


Example 3: Input Raw Data Inside the PERCENTILE Function

Also, we can find the percentile by giving raw data as an array directly to the PERCENTILE function.

Watch the following steps.

Steps:

➤ For the previous data and k= 0.4 type the following formula in Cell C2:

=PERCENTILE({11,12,13,14,15,16}, 0.4)

➤ Click the Enter button for the output.

➤ If we use k=40% in the following formula then we’ll get the same result too.

=PERCENTILE({11,12,13,14,15,16},40%)


Similar Readings


An Example of Excel PERCENTILE Function: Calculate the 80th Percentile

Let’s get introduced to another dataset which we will use for this demonstration. I have placed some employees’ names and their salaries in my dataset.

Excel PERCENTILE Function: Calculate the 80th Percentile

Now we’ll find the 80th percentile using the PERCENTILE function for this dataset. So we’ll use 0.8 or 80% for the value of k.

Steps:

➤ Write the following formula in Cell C13:

=PERCENTILE($C$5:$C$10,C12)

➤ Finally, just hit the Enter button.

So the 80th percentile turns out to be 7,832 USD.


PERCENTILE.INC and PERCENTILE.EXC Functions to Calculate Percentile in Excel

There are two more additional functions for the calculation of Percentile in Excel from the 2010 version. These are in truth, improvisations of the old PERCENTILE function. They are:
1. PERCENTILE.INC works the same way as the PERCENTILE function does.
2. PERCENTILE.EXC returns output for any k that is between 1/(n+1) to n/(n+1), where n is the number of values in an array.

In the following sections, we will see examples of each of these two functions.


1. PERCENTILE.INC Function for k= 0 to 1

The Syntax of PERCENTILE.INC Function:

=PERCENTILE.INC(array, k)

Where,

array= This is the array or range of data that defines the relative standing(required argument).

k= The percentile value in the range 0…1(required argument).

In the following example, we will calculate the 95th percentile using the PERCENTILE.INC function. Let’s see the following steps.

Steps:

➤ Activate Cell C13.

➤ Type the following formula given below-

=PERCENTILE.INC($C$5:$C$10,C12)

➤ Then just hit the Enter button.

PERCENTILE.INC Function for k= 0 to 1

You will see that the output is $8498 which means the other 95% of employees’ salary is lower than it.


2. PERCENTILE.EXC Function for k= 1/(n+1) to n/(n+1)

EXC stands for exclusive. That means It will exclude percentages from 0 to 1/(n+1) and n/(n+1) to 1. Here n is the size of the input array. On the other hand, PERCENTILE.INC can include the full range from 0 to 1 as valid k values. That’s why the result will be different from the PERCENTILE.INC function.

The Syntax of PERCENTILE.EXC Function:

=PERCENTILE.EXC(array, k)

Where,

array= This is the array or range of data that defines the relative standing(required argument).

k= The percentile value in the range 1/(n+1) to n/(n+1)(required argument).
In the following example, we will calculate the 70th percentile using the PERCENTILE.EXC function. Let’s see the following steps.

Steps:

➤ Write the following formula in Cell C13

=PERCENTILE.EXC($C$5:$C$10,C12)

➤ Finally, just press the Enter button.

PERCENTILE.EXC Function for k= 1/(n+1) to n/(n+1)

Then you will get the output like the image below-


Things to Remember

  • If k is non-numeric then the PERCENTILE function will return the #VALUE! error.
  • If k < 0 or if k > 1, the PERCENTILE function will return the #NUM! error.
  • If k is not a multiple of 1/(n – 1), the PERCENTILE function will interpolate to determine the value at the kth percentile.
  • If the supplied array is empty, it will return #NUM! error too.
  • Microsoft classifies PERCENTILE as a “compatibility function“, now replaced by the PERCENTILE.INC function.

Conclusion

I hope all of the methods described above will be good enough to use the PERCENTILE Function in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo