Using the Excel PERCENTILE.INC Function


The Excel PERCENTILE.INC 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.INC function, along with PERCENTILE.EXC, should be used as opposed to the PERCENTILE function, which is still available for compatibility purposes.


=PERCENTILE.INC (array, k)

Syntax Breakdown

Required. An array or range of data.

Required. Percentile value in the range of 0 (zero) to 1, inclusive. K can be supplied as a decimal (0.2) or a percentage (20%).

Usage Notes

PERCENTILE.INC 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 20th percentile is the value below which 20% of the observations may be found.

The function will return the #NUM! error if array is empty.

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.