Using the Excel PERCENTILE.EXC Function
SummaryThe Excel PERCENTILE.EXC returns the k-th percentile of values in a range. A percentile is a value below which a given percentage of values fall. This function is useful is you must establish a threshold of acceptance. For example, a coach may only want to recruit players who are above the the 90th percentile.
The PERCENTILE.EXC function, along with PERCENTILE.INC, should be used as opposed to the PERCENTILE function, which is still available for compatibility purposes.
Syntax=PERCENTILE.EXC (array, k)
Required. An array or range of data.
Required. Percentile value in the range of 0 (zero) to 1, exclusive.
Usage NotesPERCENTILE.EXC will calculate the k-th percentile for a set of data. When using the function, you will need to supply a range of data and a K value to represent the percent.
A percentile is a measurement from statistics. It indicates the value below which a given percentage of observations, the data set, in a group of observations fall. For example, the 90th percentile is the value below which 90% of the observations may be found.
The function will return the #NUM! error if array is empty.
PERCENTILE.EXC and PERCENTILE.INC
The K value in PERCENTILE.EXC is considered exclusive because the function excludes percentages from 0 (zero) to 1/(n + 1) and N/(n + 1) to 1, where N is the size of the input array.
PERCENTILE.INC includes the full range from 0 (zero) to 1 as valid K values.
Notes Regarding K
1) K must be a numeric value. Non-numeric values will result in the function returning the #VALUE! error.
2) The function returns the #NUM! error if K < 0 or K > 1.
3) The function interpolates to determine the value at the k-th percentile if K is not a multiple of 1/(n - 1). If Excel cannot interpolate for the k specified, the #NUM! error is returned.