Table of Contents
PERCENTILE.EXC and PERCENTILE.INC are two statistical functions in Microsoft Excel used for calculating the value at a given percentile in a data set. However, there are some key differences between these two functions.
PERCENTILE.EXC calculates the percentile by excluding the values at the boundary, whereas PERCENTILE.INC includes the values at the boundary. This means that PERCENTILE.EXC is more suitable for continuous data sets, whereas PERCENTILE.INC is more appropriate for discrete data sets.
Additionally, PERCENTILE.EXC returns an error when the specified percentile is not a multiple of 1/(n+1), where n is the number of data points. On the other hand, PERCENTILE.INC interpolates between the two nearest data points if the specified percentile is not a multiple of 1/(n+1).
In summary, the main difference between PERCENTILE.EXC and PERCENTILE.INC in Excel lies in how they handle boundary values and non-multiple percentile values. It is important to choose the appropriate function based on the type of data set being analyzed.
PERCENTILE.EXC vs. PERCENTILE.INC in Excel: What’s the Difference?
The nthpercentile 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.
Cite this article
stats writer (2024). What are the differences between PERCENTILE.EXC and PERCENTILE.INC in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/what-are-the-differences-between-percentile-exc-and-percentile-inc-in-excel/
stats writer. "What are the differences between PERCENTILE.EXC and PERCENTILE.INC in Excel?." PSYCHOLOGICAL SCALES, 2 May. 2024, https://scales.arabpsychology.com/stats/what-are-the-differences-between-percentile-exc-and-percentile-inc-in-excel/.
stats writer. "What are the differences between PERCENTILE.EXC and PERCENTILE.INC in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/what-are-the-differences-between-percentile-exc-and-percentile-inc-in-excel/.
stats writer (2024) 'What are the differences between PERCENTILE.EXC and PERCENTILE.INC in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/what-are-the-differences-between-percentile-exc-and-percentile-inc-in-excel/.
[1] stats writer, "What are the differences between PERCENTILE.EXC and PERCENTILE.INC in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, May, 2024.
stats writer. What are the differences between PERCENTILE.EXC and PERCENTILE.INC in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.