Round to the next multiple of something — up to the nearest $5, down to the nearest 100. CEILING always rounds up to a multiple, FLOOR always down. MROUND goes to the nearest.
The example
Prices rounded up to the next $5.
| A | B | |
|---|---|---|
| 1 | Value | Up to $5 |
| 2 | 12 | 15 |
| 3 | 21 | 25 |
The formula
The formula:
How it works
How it works:
CEILING(value, multiple)rounds up to the nearest multiple — 12 → 15.FLOOR(value, multiple)rounds down — 12 → 10.MROUND(value, multiple)goes to the nearest multiple either way.- Use them for price points ($0.99 endings), packaging (round up to a full case), or time blocks.
Negative numbers behave differently across CEILING variants. The modern CEILING.MATH(value, multiple, mode) lets you control how negatives round; plain CEILING rounds toward zero or away depending on the version — test if your data has negatives.
Try it: interactive demo
Value and multiple.
Variations
Round down
FLOOR:
Nearest
MROUND:
.99 price point
Next dollar minus a cent:
Pitfalls & errors
Negatives are tricky. CEILING/FLOOR handle negative values differently by version — use CEILING.MATH/FLOOR.MATH for control.
Multiple sign. In older Excel, value and multiple must share a sign or CEILING errors.
CEILING up, FLOOR down. Don’t confuse them with ROUNDUP/ROUNDDOWN (which round to decimals, not multiples).
Practice workbook
Frequently asked questions
How do I round up to the nearest multiple in Excel?
What's the difference from ROUNDUP?
How do I handle negative numbers?
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