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.

**Download Practice Workbook**

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

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