Statistical & Aggregation
Interpolated Percentile Rank: PERCENTRANK.INC vs PERCENTRANK.EXC Formulas
Compute exact inclusive [0, 1] and exclusive (0, 1) percentile ranks with linear interpolation between bracketing observations using COUNTIF, MAXIFS, and MINIFS.
How linear interpolation works between bracketing sample values
Simple empirical rank COUNTIF(range, "<" & x) / (n - 1) works only when target value x appears in the sample. When evaluating an arbitrary benchmark score x (such as 65 against sample {20, 40, 50, 80, 100}), Excel’s PERCENTRANK.INC and PERCENTRANK.EXC locate the lower bracket lo = MAXIFS(range, range, "<=" & x) and upper bracket hi = MINIFS(range, range, ">=" & x) and interpolate linearly by frac = (x - lo) / (hi - lo).
Inclusive PERCENTRANK.INC maps the sample minimum and maximum to 0.000 and 1.000 via (rank_lo + frac) / (n - 1), whereas exclusive PERCENTRANK.EXC maps them strictly inside (0, 1) via (rank_lo + 1 + frac) / (n + 1), which avoids 0% and 100% boundary infinities when feeding percentile ranks into inverse-normal transformations.
| Target Value (x) | Brackets [lo, hi] | Interpolation frac | PERCENTRANK.INC | PERCENTRANK.EXC |
|---|---|---|---|---|
| 20 (Sample Min) | [20, 20] | 0.00 | 0 / 4 = 0.0000 | 1 / 6 = 0.1667 |
| 50 (Median) | [50, 50] | 0.00 | 2 / 4 = 0.5000 | 3 / 6 = 0.5000 |
| 65 (Interpolated) | [50, 80] | 0.50 | 2.5 / 4 = 0.6250 | 3.5 / 6 = 0.5833 |
| 100 (Sample Max) | [100, 100] | 0.00 | 4 / 4 = 1.0000 | 5 / 6 = 0.8333 |
Exact Inclusive Percentile Rank (PERCENTRANK.INC)
Computes interpolated [0, 1] percentile rank of E2 within distinct sample B2:B6, returning "Out of Sample Bounds" if E2 falls outside [MIN, MAX].
=IF(OR(E2 < MIN(B2:B6), E2 > MAX(B2:B6)), "Out of Sample Bounds", LET(lo, MAXIFS(B2:B6, B2:B6, "<=" & E2), hi, MINIFS(B2:B6, B2:B6, ">=" & E2), kLo, COUNTIF(B2:B6, "<" & lo), frac, IF(hi = lo, 0, (E2 - lo) / (hi - lo)), ROUND((kLo + frac) / (COUNT(B2:B6) - 1), 4)))Exact Exclusive Percentile Rank (PERCENTRANK.EXC)
Computes interpolated (0, 1) percentile rank on denominator n + 1 so extreme sample values stay strictly between 0 and 1.
=IF(OR(E2 < MIN(B2:B6), E2 > MAX(B2:B6)), "Out of Sample Bounds", LET(lo, MAXIFS(B2:B6, B2:B6, "<=" & E2), hi, MINIFS(B2:B6, B2:B6, ">=" & E2), kLo, COUNTIF(B2:B6, "<" & lo), frac, IF(hi = lo, 0, (E2 - lo) / (hi - lo)), ROUND((kLo + 1 + frac) / (COUNT(B2:B6) + 1), 4)))Try the example
Use the original values or change them to test your understanding.