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:

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.

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

**How to Use MODE Function in Excel (4 Examples)****Use VAR Function in Excel (4 Examples)****How to Use PROB Function in Excel (3 Examples)****Use Excel STDEV Function (3 Easy Examples)****How to Use Excel GROWTH Function (4 Easy Methods)**

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

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.

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.