PERCENTILE.EXC vs. PERCENTILE.INC in Excel: What’s the Difference?

The n^th percentile of a dataset is the value that cuts off the first n percent of the data values when all of the values are sorted from least to greatest.

For example, the 90th percentile of a dataset is the value that cuts of the bottom 90% of the data values from the top 10% of data values.

There are three different functions you can use to calculate percentiles in Excel:

1. PERCENTILE.EXC: This function returns the k^th percentile of a dataset, excluding the values 0 and 1.

2. PERCENTILE.INC: This function returns the k^th percentile of a dataset, including the values 0 and 1.

3. PERCENTILE: This function returns the k^th percentile of a dataset as well. It will return the exact same value as the PERCENTILE.INC function.

The following example shows how to use the various PERCENTILE functions in Excel.

Example: PERCENTILE.EXC vs. PERCENTILE.INC in Excel

Suppose we have the following dataset in Excel:

The following screenshot shows how to calculate the 20^th percentile for the dataset using the three different percentile formulas:

Using the PERCENTILE or PERCENTILE.INC functions, we calculate the 20^th percentile to be 6.

Using the PERCENTILE.EXC function we calculate the 20^th percentile to be 5.4.

When to Use PERCENTILE.EXC vs. PERCENTILE.INC

In almost all cases, it makes more sense to use the PERCENTILE.INC function because this function includes the values 0 and 1 when calculating the percentiles.

It’s also worth nothing that both the R programming language and the Python programming language use formulas to calculate percentiles that match the PERCENTILE.INC function in Excel.

The following tutorials explain how to calculate the percentiles of a dataset in both R and Python:

• How to Calculate Percentiles in R
• How to Calculate Percentiles in Python

No matter which function you use to calculate percentiles, the difference between the values calculated by PERCENTILE.INC and PERCENTILE.EXC will be very similar in most cases.

In some cases, it’s even possible that the two functions will return the same values depending on the sequence of numbers in the dataset.

Additional Resources

STDEV.P vs. STDEV.S in Excel: What’s the Difference?
VAR.P vs. VAR.S in Excel: What’s the Difference?
Percentile vs. Quartile vs. Quantile: What’s the Difference?