How to round to the nearest 5 with MROUND

For clean pricing or time in set blocks, you want the nearest 5, or 10, or 0.25, rather than the nearest whole number. MROUND does exactly that.

The formula

=MROUND(B2,5) in column C

What each part does

MROUND
Rounds to the closest multiple of the number you give it.
5
The step size.

How it works, and what to watch for

MROUND rounds to the closest multiple of whatever you give it, so a 5 gives you the nearest five either way.

To always round up to the next block, CEILING is the one; to always round down, FLOOR.

Rounding for display only? You may be better changing the cell format, which keeps the exact figure underneath for later math.

Questions

How do I always round up to the next 5?

Use CEILING(A2,5). MROUND goes to the nearest either way; CEILING only ever goes up.

What about the nearest 25 cents?

MROUND(A2,0.25) works the same way with a fractional step.

The example sheet

Item Raw price Shelf price
Mug 12.40
Notebook 7.80
Lamp 33.10

If this helped, these are close by

More on MROUND, and every walkthrough that uses it.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.