Excel PERCENTILE.INC vs PERCENTILE.EXC

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

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *