Statistical
Exclusive Percentile and Quartile Interpolation (PERCENTILE.EXC & QUARTILE.EXC Formula)
Compute Weibull (N + 1) * k exclusive percentiles and quartiles alongside PERCENTILE_INC so small-sample tail cutoffs match engineering and hydrological standards.
How PERCENTILE.EXC differs from PERCENTILE.INC
PERCENTILE.INC maps the minimum sample value to k = 0 and the maximum to k = 1 using rank position 1 + (N - 1) * k. By contrast, PERCENTILE.EXC (and QUARTILE.EXC for k = 0.25, 0.50, 0.75) treats the N observations as dividing the population into N + 1 equal probability intervals, placing rank position at rk = (N + 1) * k.
Because rk must lie between 1 and N, PERCENTILE.EXC is strictly defined for 1 / (N + 1) <= k <= N / (N + 1) and returns #NUM! for extreme tail probabilities outside that open range.
| Percentile (k) | Exclusive Rank (N+1)*k (N=7) | PERCENTILE.EXC Value | PERCENTILE_INC Value |
|---|---|---|---|
| 0.25 (Q1) | 8 * 0.25 = 2.0 | 18.0 | 21.0 |
| 0.40 (P40) | 8 * 0.40 = 3.2 | 25.2 | 26.4 |
| 0.50 (Q2 / Median) | 8 * 0.50 = 4.0 | 30.0 | 30.0 |
| 0.75 (Q3) | 8 * 0.75 = 6.0 | 54.0 | 48.0 |
| 0.10 (< 1/8) | 8 * 0.10 = 0.8 | #NUM! | 15.6 |
Exact PERCENTILE.EXC Linear Interpolation Formula
Computes Weibull rank rk = (COUNT(A2:A8) + 1) * D2, interpolates linearly between SMALL(A2:A8, INT(rk)) and SMALL(A2:A8, INT(rk) + 1), and guards out-of-range tails.
=LET(n_obs, COUNT(A2:A8), rk, (n_obs + 1) * D2, IF(OR(rk < 1, rk > n_obs), "#NUM!", LET(lo_idx, INT(rk), frac, rk - lo_idx, IF(frac = 0, SMALL(A2:A8, lo_idx), ROUND(SMALL(A2:A8, lo_idx) + frac * (SMALL(A2:A8, lo_idx + 1) - SMALL(A2:A8, lo_idx)), 4)))))Side-by-Side Inclusive Percentile Comparison
Evaluates native PERCENTILE_INC on the same sample so analysts can see the spread difference in small cohorts.
=ROUND(PERCENTILE_INC(A2:A8, D2), 4)Try the example
Use the original values or change them to test your understanding.