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

Get FREE Advanced Excel Exercises with Solutions!

Calculating percentiles is a 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.

## What is Percentile: A Brief Idea

Percentile is a value on a scale of 100 that indicates the percentage 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, it means that it is higher than the 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:

Now, I will find here the 10th percentile. We can find it in 3 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.

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

### 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).

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.

Then, we will get the result like the image below.

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 of 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%)`

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

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

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

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.

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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. 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

Advanced Excel Exercises with Solutions PDF