Clamp a number so it never goes below a floor or above a ceiling.
Copy this formula
=MIN(MAX(A2, 0), 100)MAX(A2, 0) pulls anything below zero up to zero, and MIN(…, 100) pushes anything above 100 down to 100. Nesting them clamps the value into the range — useful for scores, percentages, and capped bonuses.
=MIN(MAX(A2, 1), 5)→5Clamps a rating to 1–5.
=MIN(MAX(120, 0), 100)→100A value above the ceiling is cut down to 100.
=MIN(MAX(-15, 0), 100)→0A value below the floor is raised to 0.
Yes — use just =MAX(A2, 0) to floor at zero without a ceiling.
Yes. Use =MIN(A2, 100) to apply a ceiling without a floor.
Use =MIN(A2, 1), because 100% is stored as 1.
No. Excel has no clamp function, so nest MIN and MAX. =MEDIAN(0, A2, 100) gives the same result.
Need a formula for your own data?
Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.