# The Excel PERCENTILE.EXC Function

Related Functions:
PERCENTILE.INC
QUARTILE.EXC
PERCENTILE.INC & PERCENTILE.EXC Functions

The Percentile.Inc and Percentile.Exc functions both find the k'th percentile of a supplied data set.

The difference between these two functions is that, in the Percentile.Inc function the value of k is is within the range 0 to 1 inclusive, and in the Percentile.Exc function, the value of k is within the range 0 to 1 exclusive.

## Function Description

The Excel Percentile.Exc function returns the k'th percentile (i.e. the value below which k% of the data values fall) for a supplied range of values and a supplied k (between 0 & 1 exclusive).

The function is new in Excel 2010 and so is not available in earlier versions of Excel.

The syntax of the Percentile.Exc function is:

PERCENTILE.EXC( array, k )

Where the function arguments are:

 array - The array of data values for which you want to calculate the k'th percentile. k - The value, between 0 and 1 (exclusive), of the required percentile.

If k is not a multiple of 1/(n+1), (where n is the number of values in the supplied array), the Percentile.Exc function interpolates between the values in the supplied array, to calculate the percentile value. However, if k is < 1/(n+1) or k is > n/(n+1), the function is unable to interpolate, and so returns an error.

## Percentile.Exc Function Examples

Column B of the spreadsheet below shows four examples of the Excel Percentile.Exc function, used to calculate the k'th percentile of the set of values in cells A1-A4. In each case, a different value of k is specified.

Formulas:
AB
11=PERCENTILE.EXC( A1:A4, 0.2 )
22=PERCENTILE.EXC( A1:A4, 60% )
33=PERCENTILE.EXC( A1:A4, 50% )
44=PERCENTILE.EXC( A1:A4, 95% )
Results:
AB
111
223
332.5
44#NUM!

Note that in the above examples:

• The value of k can be input as a decimal or a percentage. For example, value 0.2 in cell B1, is the same as 20% (the 20th percentile);
• The 50th percentile (see cell B3) falls half way between the values of 2 and 3. Therefore, in this case, Excel has interpolated, to calculate the result 2.5;
• When k is 95% (see cell B4), the function returns the #NUM! error. This is because 95% (or 0.95) is greater than the maximum allowed value of k, which is 0.8 (= n/(n+1)).

For further examples of the Excel Percentile.Exc function, see the Microsoft Office website

## Percentile.Exc Function Errors

If you get an error from the Excel Percentile.Exc function this is likely to be one of the following:

Common Errors
 #NUM! - Occurs if either:The supplied value of k is < 1/(n+1) or > n/(n+1)(where n is the number of values in the supplied array)orThe supplied array is empty. #VALUE! - Occurs if the supplied value of k is non-numeric.