MROUND in Excel: what it does and how to use it

MROUND rounds a number to the nearest multiple of whatever you choose: the nearest five, the nearest quarter hour, the nearest fifty.

Syntax

MROUND(number, multiple)

When to use it

Pricing to the nearest five, timesheets to the nearest fifteen minutes, quantities to a whole case.

What trips people up

It rounds to the nearest, which means it can go up or down. When it must only ever go one way, CEILING and FLOOR are the ones to use.

Watch it built, step by step

Round prices to the nearest 5 — Tidy pricing and time blocks

Questions

How do I always round up to the nearest 5?

Use CEILING: =CEILING(A2,5) always goes up. FLOOR always goes down. MROUND goes to whichever is nearer.

Why does MROUND return #NUM!?

The number and the multiple have different signs, such as a negative number with a positive multiple. Make both negative, or use CEILING.MATH.

Can MROUND round times?

Yes. =MROUND(A2,"0:15") rounds a time to the nearest quarter of an hour.

Related functions

ROUND — ROUND rounds a number to the number of decimal places you name.

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.