Rounding to Multiples With MROUND

Sometimes you do not want to round to a number of decimals, but to the nearest multiple: the nearest 5 cents, nearest 10 dollars, or nearest quarter hour.

The MROUND function does exactly that. Instead of asking how many decimals to keep, it asks what step size to snap to. This lesson shows how to round to any multiple you like.

MROUND takes two arguments:

• number the value to round
• multiple the step to round to

It returns the nearest multiple of that step. For example =MROUND(23, 5) rounds 23 to the nearest 5, which is 25, because 23 is closer to 25 than to 20.

MROUND rounds to the nearest multiple, up or down. The halfway point goes up.

So =MROUND(22, 5) gives 20 (closer to 20), =MROUND(23, 5) gives 25, and the exact midpoint =MROUND(22.5, 5) rounds up to 25. The step size, not decimal places, drives the result.

A common retail need is pricing to the nearest 5 cents. Use 0.05 as the multiple.

=MROUND(4.93, 0.05) returns 4.95, and =MROUND(4.91, 0.05) returns 4.90. This keeps prices on tidy nickel boundaries that cash registers love.

Timesheets often round to the nearest 15 minutes. Since time in spreadsheets is a fraction of a day, 15 minutes is "0:15" or the value 1/96.

If a clocked time sits in A2, then =MROUND(A2, "0:15") snaps it to the nearest quarter hour. MROUND understands the time value and rounds it cleanly.

MROUND has one strict rule: the number and the multiple must share the same sign. Mixing a positive number with a negative multiple gives a #NUM! error.

So =MROUND(-23, -5) is fine and returns -25, but =MROUND(23, -5) errors out. Keep both arguments positive, or both negative.

How is MROUND different from ROUND? ROUND controls decimal places; MROUND controls the step size.

You can imitate ROUND with MROUND using a power of ten: =MROUND(8.47, 0.01) behaves like rounding to 2 decimals, giving 8.47. But MROUND shines when the step is not a power of ten, like 5, 25, or 0.05.

MROUND always picks the nearest multiple. If you must always go up or always go down to a multiple, use its cousins:

• CEILING(number, multiple) rounds up to a multiple
• FLOOR(number, multiple) rounds down to a multiple

So =CEILING(23, 5) gives 25 and =FLOOR(23, 5) gives 20, regardless of which is closer.

Suppose a loyalty program awards one reward per full 10 dollars spent. You want to round a spend down to the nearest 10.

=FLOOR(47, 10) returns 40, meaning 4 rewards. FLOOR guarantees you never round above the amount actually spent, which is the conservative, correct choice here.

Quick guide for multiple-based rounding:

• MROUND for the nearest multiple (up or down).
• CEILING when you must always round up to a multiple, such as packaging or capacity.
• FLOOR when you must always round down, such as full units earned.

All three take the same two arguments: the number and the step. Pick by whether direction matters.

The step does not have to be a literal number. You can store it in a cell and reference it, so changing one cell re-rounds everything.

If the rounding step lives in B1 (say 25) and the value in A2, then =MROUND(A2, $B$1) snaps A2 to the nearest 25. The dollar signs lock B1 so the formula copies cleanly down a column.

Test your understanding of MROUND.

You can now round to any step, not just decimal places:

• MROUND(number, multiple) rounds to the nearest multiple; the midpoint goes up.
• Number and multiple must share the same sign or you get #NUM!.
• CEILING always rounds up to a multiple; FLOOR always rounds down.

These are ideal for prices, quarter-hour time, and packaging quantities.