Math & Trig
Cap percentages between 0% and 100% (clamping with MEDIAN)
Clamp attainment and completion ratios cleanly between a lower floor and upper cap in one readable formula with MEDIAN(0, 1, Ratio).
Why MEDIAN(Floor, Cap, Value) replaces nested MIN(Cap, MAX(Floor, Value))
Of three numbers—0, 1, and X—the middle value sorted by size is 0 whenever X < 0, X whenever 0 <= X <= 1, and 1 whenever X > 1.
That makes MEDIAN(0, 1, Actual / Quota) a single-function two-sided clamp for progress bars, bonus caps, and SLA completion rates where net clawbacks (-15%) or over-delivery (135%) must stay within [0%, 100%].
Clamping is a display or compensation-policy choice, not a universal rule for these metrics. Preserve the raw ratio for auditing. Require a positive numeric quota and choose rounding precision deliberately; a zero quota causes division by zero.
| Actual (A) | Quota (B) | Raw Ratio | Clamped [0, 1] |
|---|---|---|---|
| 135 | 100 | 1.35 (135%) | 1 (100%) |
| 78 | 100 | 0.78 (78%) | 0.78 (78%) |
| -15 | 100 | -0.15 (-15%) | 0 (0%) |
MEDIAN clamp and custom bonus caps
The same pattern clamps to any custom band—for example, MEDIAN(0, 1.25, Actual / Quota) caps commission accelerators at 125%.
=MEDIAN(0, 1, ROUND(A2 / B2, 4))Equivalent MIN + MAX clamp
MIN(1.25, MAX(0, ROUND(A2 / B2, 4))) expresses the same bounds explicitly.
=MIN(1.25, MAX(0, ROUND(A2 / B2, 4)))Try the example
Use the original values or change them to test your understanding.