To force a number to stay within a floor and a ceiling — a percentage that can’t exceed 100, a bonus capped at $5,000, a quantity no less than zero — nest MIN and MAX. It’s the spreadsheet version of “clamp.”
MAX(A2, 0) lifts anything below 0 up to 0; the outer MIN(…, 100) pulls anything above 100 down to 100.
The example
Raw values capped to the 0–100 range.
| A | B | |
|---|---|---|
| 1 | Raw | Capped (0-100) |
| 2 | -15 | 0 |
| 3 | 72 | 72 |
| 4 | 118 | 100 |
The formula
The clamp formula:
How it works
Two functions enforce the two limits:
MAX(A2, 0)handles the floor: if A2 is below 0, MAX returns 0; otherwise it returns A2.MIN(…, 100)handles the ceiling: if the result is above 100, MIN returns 100; otherwise it passes through.- Together they guarantee the output never leaves the 0–100 band — values inside the range are untouched.
- A neat one-function alternative is
=MEDIAN(0, A2, 100), which returns the middle of the three — also a clamp.
Try it: interactive demo
Drag the raw value; watch it get clamped to 0–100.
Variations
One-function clamp with MEDIAN
The median of {low, value, high} is the clamp:
Floor only (never below zero)
Just the lower bound:
Ceiling only (cap a bonus)
Just the upper bound:
Pitfalls & errors
Order matters for readability, not result. MIN(MAX(x,lo),hi) and MAX(MIN(x,hi),lo) give the same answer — pick one and be consistent.
Swapped limits. If your low is accidentally above your high, the clamp returns the wrong bound. Make sure low ≤ high.
MEDIAN clamp needs exactly three arguments — low, value, high. Adding more changes the meaning.
Practice workbook
Frequently asked questions
How do I limit a value to a range in Excel?
Is there a single-function way to clamp a value?
How do I stop a value from going below zero?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class