PERCENTILE.EXC
Returns the k-th percentile of values in a range, where k is strictly between 0 and 1 (exclusive). Used in some statistical conventions.
PERCENTILE.EXC(array, k)Arguments
arrayThe array or range of data that defines relative standing.kThe percentile value, strictly 0 < k < 1.
=PERCENTILE.EXC({1,2,3,4,5}, 0.5)50th percentile = 3
- •Available since Excel 2010
- •Returns #NUM! if k ≤ 0, k ≥ 1, or k < 1/(n+1) or k > n/(n+1)
- •Use PERCENTILE.INC for the inclusive variant (matches legacy PERCENTILE)
- •Passing a k value at or beyond 0 or 1 - unlike PERCENTILE.INC, PERCENTILE.EXC requires k to be strictly between those bounds, and even values very close to them can raise a #NUM! error depending on the dataset's size
- •Using PERCENTILE.EXC on a small dataset without realizing that its valid k range actually depends on the number of data points - very low or high percentiles can be mathematically impossible to compute for small n
- •Assuming PERCENTILE.EXC and PERCENTILE.INC always give similar results - they can diverge meaningfully, especially for small datasets or percentiles near the extremes
PERCENTILE.INCIncludes the full 0 to 1 range for k, a more permissive alternative to PERCENTILE.EXC's stricter exclusive method.QUARTILE.EXCCalculates specific percentiles (25th, 50th, 75th) using the same exclusive convention as PERCENTILE.EXC, but with simpler preset arguments.MEDIANEquivalent to PERCENTILE.EXC with k set to 0.5, a more direct way to get specifically the middle value.Why does PERCENTILE.EXC return a #NUM! error even though k looks like it's between 0 and 1?
PERCENTILE.EXC's valid range for k depends on the dataset size - specifically, k must be between 1/(n+1) and n/(n+1). For small datasets, this excludes a wider range near 0 and 1 than you might expect.
When should I use PERCENTILE.EXC instead of PERCENTILE.INC?
Use PERCENTILE.EXC when following a statistical convention that specifically calls for excluding the dataset's endpoints from the percentile calculation - otherwise PERCENTILE.INC, which matches the legacy PERCENTILE function, is the more common default.
Can PERCENTILE.EXC and PERCENTILE.INC give different results for the same data?
Yes, they use different underlying formulas, so results can diverge, particularly for small datasets or percentiles close to the minimum or maximum values.
Need to translate a formula using PERCENTILE.EXC?
Use our translator to convert your complete formula