What is the difference between PERCENTILE.EXC vs. PERCENTILE.INC in Excel?

By /

The difference between PERCENTILE.EXC and PERCENTILE.INC in Excel lies in how they treat the data. PERCENTILE.EXC excludes the value in the percentile calculation, while PERCENTILE.INC includes the value. Therefore, PERCENTILE.EXC will always return a value lower than or equal to the percentile, while PERCENTILE.INC will return a value higher than or equal to the percentile.

The nth 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 kth percentile of a dataset, excluding the values 0 and 1.

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

3. PERCENTILE: This function returns the kth 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 20th percentile for the dataset using the three different percentile formulas:

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

Using the PERCENTILE.EXC function we calculate the 20th 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.

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.

x

Related terms:

  1. What’s the difference between percentile, quartile, and quantile?
  2. What is the difference between Within-Group and Between Group Variation in ANOVA?
  3. Kepner-Tregoe Inc.
  4. How to Perform a Percentile IF Function in Excel
  5. How to calculate the 90th percentile in Excel?
  6. DAUBERT V MERRELL DOW PHARMACEUTICALS INC
  7. AMERICAN PARKINSON DISEASE ASSOCIATION, INC
  8. Excel: Calculate Difference Between Two Times in Seconds
  9. What is the difference between SUMIF and SUMIFS in Excel?
  10. How to calculate the difference between two dates in Excel?