Excel MROUND, CEILING & FLOOR — Round to the Nearest Multiple, Not a Decimal

TL;DR — MROUND, CEILING and FLOOR round to the nearest multiple of a number you choose, not to a count of decimal places. =MROUND(127, 5) is 125 (nearest multiple of 5); =CEILING.MATH(127, 5) is 130 (always up to the next 5); =FLOOR.MATH(127, 5) is 125 (always down). The headline trap: the second argument is a multiple ("significance"), not digits — a different mental model from ROUND. Use the modern CEILING.MATH / FLOOR.MATH; the legacy CEILING/FLOOR throw #NUM! when the number and the multiple have opposite signs. MROUND works in all versions; the .MATH pair is Excel 2013+.

=MROUND(127, 5)        ' -> 125   nearest multiple of 5
=CEILING.MATH(2.10, 0.05)   ' -> 2.10   round a price up to the next 5 cents
=FLOOR.MATH(9:47, "0:15")   ' -> 9:45   floor a time to the quarter hour

ROUND rounds to a number of decimal places. But a huge amount of real-world rounding isn't about decimals at all — it's about multiples: prices that must land on a 5-cent or 95-cent boundary, time clocked to the nearest quarter hour, items packed in full cartons of 12, quantities ordered by the pallet. Those are the jobs MROUND, CEILING and FLOOR were built for, and once you see them as "snap to a grid" they stop being confusing. This guide leads with that mental model, then the two traps that catch everyone.

What you'll learn

The mental model: these snap a number to a grid of multiples
Why the second argument is a multiple, not a number of decimals
MROUND (nearest) vs CEILING (always up) vs FLOOR (always down)
The #NUM! trap on legacy CEILING/FLOOR, and why .MATH is safer
How negatives behave — and the mode argument that flips them
The judgment call: pricing, scheduling, and packing

The mental model: snap to a grid

Imagine a ruler marked only at multiples of your chosen number — every 5, every 0.25, every 12. These three functions take any value and snap it to a tick on that ruler. The only difference between them is which tick they pick:

MROUND → the nearest tick (could be up or down)
CEILING → the next tick up (away from zero, by default)
FLOOR → the next tick down (toward zero, by default)

' multiples of 5:  ...  120   125   130  ...
=MROUND(127, 5)        ' -> 125   (127 is closer to 125)
=CEILING.MATH(127, 5)  ' -> 130   (next 5 up)
=FLOOR.MATH(127, 5)    ' -> 125   (next 5 down)

That's the whole idea. The "significance" argument is the spacing of the ruler's ticks — which is why calling it "decimal places" leads you wrong.

The trap that defines them: multiple, not digits

This is the single most common mistake, because the argument position looks like ROUND's but the meaning is completely different.

=ROUND(2.346, 2)       ' -> 2.35   2 = decimal places
=MROUND(2.346, 2)      ' -> 2      2 = nearest multiple of 2
=MROUND(2.346, 0.01)   ' -> 2.35   THIS is "to the nearest cent" with MROUND

ROUND(x, 2) keeps two decimals. MROUND(x, 2) snaps to the nearest even number. If you want MROUND to behave like "nearest cent," the multiple is 0.01, not 2. Whenever a multiple result looks wildly off, check this first: you almost certainly passed a decimal count where a multiple belongs.

MROUND, CEILING, FLOOR: which tick

The direction is the decision, and it usually maps straight onto a business rule:

' Price psychology: land every price on x.95
=CEILING.MATH(12.40, 1) - 0.05      ' -> 12.95
' Time clocking: round worked time to the nearest 15 minutes
=MROUND(B2, "0:15")
' Shipping: you can't send a partial carton of 12
=CEILING.MATH(orders, 12)           ' 40 orders -> 48 (4 cartons)
' Budget: how many whole units fit in the cash you have
=FLOOR.MATH(budget / unit_cost, 1)  ' floor to a whole unit

The judgment is almost always about which direction the error must go. Packing and capacity round up (CEILING) — running one carton short is the failure. Affordability and "how many complete X" round down (FLOOR) — promising one more than you can deliver is the failure. When either side is fine and you just want the closest, MROUND.

The #NUM! trap: why CEILING.MATH and FLOOR.MATH exist

The original CEILING and FLOOR have a sharp edge: if the number and the significance have opposite signs, they don't round — they error.

=CEILING(-4.1, 1)      ' -> #NUM!   negative number, positive significance
=CEILING.MATH(-4.1, 1) ' -> -4      just works

That #NUM! is the top "why does CEILING break on my negatives?" surprise, and it's exactly why Excel 2013 introduced CEILING.MATH and FLOOR.MATH. The .MATH versions handle any sign gracefully and add an optional mode argument. Use CEILING.MATH and FLOOR.MATH for new work; keep the bare CEILING/ FLOOR only when an old workbook already depends on them. (MROUND has its own version of this rule — its number and multiple must share a sign, or you get #NUM! too.)

How negatives round, and the mode switch

By default, CEILING.MATH rounds toward positive infinity and FLOOR.MATH toward negative infinity — the true mathematical ceiling and floor, sign included:

=CEILING.MATH(-4.1, 1)     ' -> -4    (toward +infinity: -4 is higher)
=FLOOR.MATH(-4.1, 1)       ' -> -5    (toward -infinity: -5 is lower)
=CEILING.MATH(-4.1, 1, 1)  ' -> -5    mode = 1 rounds AWAY from zero instead

The optional third argument, mode, flips negative numbers to round away from zero instead of toward infinity. You rarely need it, but when a spec says "always round magnitude up regardless of sign," that's the switch. This toward-infinity-vs-toward-zero distinction is the same one that separates INT from TRUNC; see Excel INT, TRUNC & MOD. And if you were rounding to decimals rather than a multiple, you want Excel ROUND, ROUNDUP & ROUNDDOWN.

The judgment call: multiple vs decimal vs constrain

Ask two questions. First: am I rounding to a multiple (5 cents, 15 minutes, 12-packs) or to a number of decimals? Multiples → this family; decimals → ROUND. Second: does the result have to go a particular direction? Must-not-undershoot → CEILING.MATH; must-not-overshoot → FLOOR.MATH; closest is fine → MROUND. Default to the .MATH functions so a stray negative never turns into #NUM!, and reserve plain CEILING/FLOOR for legacy files. Naming the multiple and the direction in words first makes the formula almost write itself.

How ExcelMaster helps

"Round every price up to the next 95 cents," "clock time to the nearest quarter hour," "order in whole pallets of 48" — these are multiple-and-direction rules, and the bugs come from passing a decimal count instead of a multiple or hitting #NUM! on a negative. ExcelMaster turns the sentence into the right call: MROUND when closest wins, CEILING.MATH/FLOOR.MATH when a direction is mandated, with the multiple set correctly and signs handled. You state the rule in plain terms; it picks the function that snaps to the grid you meant.

Frequently asked questions

How do I round to the nearest 5 (or 5 cents) in Excel?

Use MROUND with the multiple as the second argument: =MROUND(A2, 5) for the nearest 5, or =MROUND(A2, 0.05) for the nearest 5 cents. For "always up" use =CEILING.MATH(A2, 5); for "always down" use =FLOOR.MATH(A2, 5).

What's the difference between MROUND and ROUND?

ROUND's second argument is a number of decimal places; MROUND's is a multiple to snap to. =ROUND(x, 2) keeps two decimals, while =MROUND(x, 2) rounds to the nearest multiple of 2. For "nearest cent" with MROUND, use a multiple of 0.01.

Why does CEILING return #NUM!?

The legacy CEILING/FLOOR error when the number and the significance have opposite signs (e.g. a negative number with a positive multiple). Use CEILING.MATH / FLOOR.MATH, which handle any sign.

How do I round time to the nearest 15 minutes?

Use =MROUND(time, "0:15") for the nearest quarter hour, =CEILING.MATH(time, "0:15") to always round up, or =FLOOR.MATH(time, "0:15") to always round down. Times are fractions of a day, so the "0:15" text resolves to the right value.

Should I use CEILING or CEILING.MATH?

CEILING.MATH for new work — it handles negatives without erroring and adds a mode argument. Keep plain CEILING only when an existing workbook already relies on it.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-27.