Statistical
Conditional Percentile (PERCENTILE IF) with FILTER for P90 SLA and pay bands
Calculate P90, P75, or P25 percentiles for a specific department, region, or service tier by nesting FILTER inside PERCENTILE.INC.
Why Excel has no built-in PERCENTILEIFS—and how FILTER solves it cleanly
PERCENTILE.INC takes a set of observations and a percentile between 0 and 1. FILTER can select a service or department first, so the percentile is calculated only over that group.
Nesting FILTER(Values, CriteriaRange = Target) inside PERCENTILE.INC(array, k) passes ONLY the matching subset into the percentile interpolator—no legacy Ctrl+Shift+Enter IF array needed!
| Service Tier (A) | Latency ms (B) | Included for "API"? | API Percentile Benchmarks |
|---|---|---|---|
| API (5 samples) | 120, 140, 180, 220, 340 | Yes (n = 5) | P50 = 180 ms | P75 = 220 ms |
| API P90 Interpolation | Index 1 + 0.9*(5-1) = 4.6 | 220 + 0.6*(340-220) | P90 = 292 ms |
| Auth (2 samples) | 90, 110 | Excluded | P90 ("Auth") = 108 ms |
Compute conditional P90 or P75 for a matching category
E2 holds the target service ("API") and F2 holds the percentile k (0.90 for P90).
Only numeric latency observations are included; zero is retained. PERCENTILE.INC linearly interpolates between sorted observations. Five measurements illustrate the arithmetic, not a reliable production SLA estimate. Missing groups return a message instead of a fabricated zero.
=IF(OR(NOT(ISNUMBER(F2)),F2<0,F2>1),"Invalid percentile",IF(SUMPRODUCT((A2:A8=E2)*ISNUMBER(B2:B8))=0,"No numeric observations",PERCENTILE.INC(FILTER(B2:B8,(A2:A8=E2)*ISNUMBER(B2:B8)),F2)))Compute conditional interquartile range (IQR = P75 - P25) by group
Binding the filtered subset once inside LET avoids scanning the table twice when calculating group dispersion (P75 minus P25).
=IF(SUMPRODUCT((A2:A8=E2)*ISNUMBER(B2:B8))=0,"No numeric observations",LET(sub,FILTER(B2:B8,(A2:A8=E2)*ISNUMBER(B2:B8)),PERCENTILE.INC(sub,0.75)-PERCENTILE.INC(sub,0.25)))Try the example
Use the original values or change them to test your understanding.