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.
<< Go Back to Excel Functions | Learn Excel
