To use PERCENTILE.EXC, provide a range of values and a number between 0 and 1 for the “k” argument, which represents percent. For example: You can also specify k as a percent using the % character: In the example shown, the formula in G5 is: where “scores” is the named range C5:C14.
PERCENTILE.INC vs. PERCENTILE.EXC
The reason the PERCENTILE.EXC function is exclusive is because the function excludes percentages from 0 to 1/(N+1) as well as N/(N+1) to 1, where N is the size of the input array. On the other hand, PERCENTILE.INC includes the full range from 0 to 1 as valid k values.
Effectively, PERCENTILE.EXC will always choose a value farther away from the mean of the data set, compared to PERCENTILE.INC. Note that both functions map to the full range of data.
Error Values
PERCENTILE.EXC will return the #NUM error if k is less than 1/(n+1) or greater than n/(n+1). In the example shown, where the array contains 10 values, the minimum value for k is 0.091 and the maximum is 0.909. Note: Microsoft classifies PERCENTILE as a “compatibility function”, now replaced by the PERCENTILE.INC function.
Notes
k can be provided as a decimal (.5) or a percentage (50%) k must be between 0 and 1, or PERCENTILE.EXC will return the #NUM! error. PERCENTILE.EXC will return the #NUM error if k is less than 1/(n+1) or greater than n/(n+1). PERCENTILE.EXC will interpolate when k is not a multiple of 1/(n+1).
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
