Math
Convert negative numbers to zero (and clamp values between Min and Max)
Replace verbose IF(x<0, 0, x) formulas with MAX(0, x) for zero floors and MEDIAN(Min, x, Max) for two-sided caps.
Use MAX when the calculation explicitly requires a zero floor
Some models explicitly require a zero floor. When that rule applies, MAX(0, expression) expresses it compactly without repeating the expression in an IF condition and branch. Do not assume that every negative adjustment should be discarded.
MAX(0, LongFormula) evaluates the expression once: if it is negative (-35), 0 is larger so MAX returns 0; if positive (240), MAX returns 240.
| RawBonus (A) | Floor (B) | Cap (C) | MAX(0, A) | MEDIAN(B, A, C) Clamp |
|---|---|---|---|---|
| -35 | 0 | 500 | 0 | 0 |
| 240 | 0 | 500 | 240 | 240 |
| 680 | 0 | 500 | 680 | 500 |
Two-sided clamping with MEDIAN(Min, Value, Max)
When a payout has BOTH a 0 floor AND a $500 cap, MEDIAN(0, Value, 500) picks the middle of the three numbers—returning 0 when Value < 0, Value when 0 <= Value <= 500, and 500 when Value > 500!
=MAX(0, A2)Verify upper cap with MEDIAN
On RawBonus = 680 in A4, MEDIAN(B4, A4, C4) sorts [0, 500, 680] and returns the middle number: 500.
=MEDIAN(B4, A4, C4)Try the example
Use the original values or change them to test your understanding.