This Excel tutorial explains how to use Percentile related Functions, include PERCENTILE, PERCENTILE.INC, PERCENTILE.EXC.
Excel Percentile
Percentile is a statistics term used to describe the Nth observation in a data set after sorting in ascending order. For example, 40th percentile (P40) refers to the approximately 40th data of the 100 data. If the data set is not exactly 100, say 200, we find the location of observation at 40% of total data.
Percentile is commonly used in salary survey to find out the 25th 50th, 75th, 90th percentile. 50% percentile is known as median, human resources department always compares P50 salary with employees’ salary so that they know who are below market compensation. Some companies may have salary guideline of P75, which means they generally pay better than the market in order to attract and retain talents. Percentile is a better measure than average because extreme data are disregarded using percentile.
There are three Functions to calculate percentile in Excel (PERCENTILE, PERCENTILE.INC, PERCENTILE.EXC). Before we look into the difference, let’s look at an example of manual calculation.
Percentile – Manual calculation
There are basically two kinds of percentile calculation. Although the results are slightly different, both are considered correct. If you want to know all other methods, click here.
Method 1 – using (n+1)*p
This is the most common approach written in college statistics text book.
To calculate percentile, find out the location (or rank) of the percentile first
Location of a Percentile = (total number of observations + 1) * (target percentile / 100)
Using the data set 1, 3, 5, 7 as example,
Location of P75 = (4+1)*(75/100)
============== 3.75
The 3.75th percentile is three quarters of the distance between the third and forth observation, therefore
Value of P75 = 3rd observation + (4th observation -3rd observation ) * 0.75
=========== 5+(7-5)*0.75
=========== 6.5
Method 2 – using (n-1)*p+1
Method 1 and Method 2 are only different when the calculated location (rank) of percentile is non-integer.
Location of a Percentile = (total number of observations - 1) * (target percentile / 100) + 1
Using the data set 1, 3, 5, 7 as example,
Location of P75 = (4-1)*(75/100)+1
============== 3.25
Value of P75 = 3rd observation + (4th observation -3rd observation ) * 0.25
=========== 5+(7-5)*0.25
=========== 5.5
Percentile – Excel calculation
There are three Functions relating to percentile in Excel :PERCENTILE, PERCENTILE.INC, PERCENTILE.EXC
If you want to calculate percentile using Method 1, use PERCENTILE.EXC Function.
If you want to calculate percentile using Method 2, use PERCENTILE or PERCENTILE.INC Function, both are the same.
PERCENTILE.EXC and PERCENTILE.INC are new versions of PERCENTILE. PERCENTILE remains in Excel for compatibility purpose only, you can ignore this Function.
Syntax
All three Functions have the same arguments
PERCENTILE(array, k)
PERCENTILE.INC(array, k)
PERCENTILE.EXC(array, k)
| array | The array or range of data that defines relative standing. |
| k | The percentile value in decimal number |
PERCENTILE.EXC works if k is between 1/n and 1-1/n, while PERCENTILE.INC works if k is between 0 and 1.
Example
Suppose data set 1, 3, 5, 7 lie in Range A1:A4. To find P75
Method 1:
= PERCENTILE.EXC(A1:A4,0.75)
= 6.5
Method 2:
= PERCENTILE.INC(A1:A4,0.75)
= 5.5