PERCENTILE.EXC and PERCENTILE.INC differs only in the way the possibly-non-integer rank is calculated.<\/p>\n\n\n\n
For PERCENTILE.INC (and PERCENTILE) the calculated rank is K*(N-1)+1.<\/p>\n\n\n\n
For PERCENTILE.EXC the calculated rank is K*(N+1).<\/p>\n\n\n\n
<\/p>\n\n\n\n
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 <\/strong>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.<\/p>\n\n\n\n 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\u2019 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.<\/p>\n\n\n\n There are three Functions to calculate percentile in Excel (PERCENTILE<\/a>, PERCENTILE.INC<\/a>, PERCENTILE.EXC<\/a>). Before we look into the difference, let\u2019s look at an example of manual calculation.<\/p>\n\n\n\n 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<\/a>.<\/p>\n\n\n\n This is the most common approach written in college statistics text book.<\/p>\n\n\n\n To calculate percentile, find out the location (or rank) of the percentile first<\/p>\n\n\n\n Using the data set 1, 3, 5, 7<\/strong> as example,<\/p>\n\n\n\n Location of P75 = (4+1)*(75\/100)<\/p>\n\n\n\n ============== 3.75<\/p>\n\n\n\n The 3.75th percentile is three quarters of the distance between the third and forth observation, therefore<\/p>\n\n\n\n Value of P75 = 3rd observation + (4th observation -3rd observation ) * 0.75<\/p>\n\n\n\n =========== 5+(7-5)*0.75<\/p>\n\n\n\n =========== 6.5<\/p>\n\n\n\n Method 1 and Method 2 are only different when the calculated location (rank) of percentile is non-integer.<\/p>\n\n\n\n Using the data set 1, 3, 5, 7<\/strong> as example,<\/p>\n\n\n\n Location of P75 = (4-1)*(75\/100)+1<\/p>\n\n\n\n ============== 3.25<\/p>\n\n\n\n Value of P75 = 3rd observation + (4th observation -3rd observation ) * 0.25<\/p>\n\n\n\n =========== 5+(7-5)*0.25<\/p>\n\n\n\n =========== 5.5<\/p>\n\n\n\n There are three Functions relating to percentile in Excel :PERCENTILE, PERCENTILE.INC, PERCENTILE.EXC<\/p>\n\n\n\n If you want to calculate percentile using Method 1, use PERCENTILE.EXC<\/strong> Function.<\/p>\n\n\n\n If you want to calculate percentile using Method 2, use PERCENTILE <\/strong>or PERCENTILE.INC<\/strong> Function, both are the same.<\/p>\n\n\n\n PERCENTILE.EXC and PERCENTILE.INC are new versions of PERCENTILE. PERCENTILE remains in Excel for compatibility purpose only, you can ignore this Function.<\/p>\n\n\n\n All three Functions have the same arguments<\/p>\n\n\n\nPercentile \u2013 Manual calculation<\/h2>\n\n\n\n
Method 1 \u2013 using (n+1)*p<\/h4>\n\n\n\n
Location of a Percentile = (total number of observations + 1) * (target percentile \/ 100)<\/pre>\n\n\n\n
Method 2 \u2013 using (n-1)*p+1<\/h4>\n\n\n\n
Location of a Percentile = (total number of observations - 1) * (target percentile \/ 100) + 1<\/pre>\n\n\n\n
Percentile \u2013 Excel calculation<\/h2>\n\n\n\n
Syntax<\/h4>\n\n\n\n
PERCENTILE(array, k)<\/pre>\n\n\n\n
PERCENTILE.INC(array, k)<\/pre>\n\n\n\n
PERCENTILE.EXC(array, k)<\/pre>\n\n\n\n