How to Use the MROUND Function in Microsoft Excel to Round Numbers to the Nearest Multiple

Standard rounding functions in Microsoft Excel—such as ROUND, ROUNDUP, and ROUNDDOWN—round numbers strictly based on decimal place precision (such as tenths, hundredths, or whole integers). However, commercial pricing structures, time tracking logs, manufacturing packaging batches, and currency denominations frequently require rounding to specific intervals (such as the nearest 5 pence, nearest quarter-hour, or nearest 24-unit carton). The MROUND function solves this by rounding values up or down to the nearest user-defined multiple.

What is the MROUND Function?

Unlike CEILING (which always forces numbers upward) or FLOOR (which always forces numbers downward), MROUND evaluates whether the remainder of a number is closer to the lower or upper multiple. If the remainder is equal to or greater than half the value of the multiple, Excel rounds up; otherwise, it rounds down.

Function	Rounding Direction	Example: value = 13, multiple = 5
MROUND	Nearest multiple (standard half-way up rule).	=MROUND(13, 5) → 15
CEILING.MATH	Always upward (away from zero).	=CEILING.MATH(13, 5) → 15
FLOOR.MATH	Always downward (towards zero).	=FLOOR.MATH(13, 5) → 10

Formula Syntax and Arguments

The syntax for MROUND is concise and requires two arguments:

=MROUND(number, multiple)

• number: The numeric value or cell reference containing the number to round.
• multiple: The numeric increment to which you want to round number.

Practical Step-by-Step Examples in Excel

1. Rounding Prices to the Nearest Nickel or Five Cents

Retail stores in countries that have phased out one-cent coins must round final cash transaction totals to the nearest 0.05. If cell A2 contains 24.62:

=MROUND(A2, 0.05)

Result: 24.60 (since 0.02 is less than half of 0.05).

If cell A2 contains 24.63:

=MROUND(A2, 0.05)

Result: 24.65.

2. Rounding Timesheets to the Nearest 15 Minutes

Payroll systems often record clock-in timestamps that must be rounded to the nearest quarter of an hour. Because Excel stores time values as fractional fractions of a 24-hour day (where 1 hour is 1/24 and 1 minute is 1/1440), you can round time using two equivalent approaches:

Method A: Using Time String Representation

=MROUND(A2, "0:15")

Method B: Using Decimal Fractions

Since 15 minutes is 15 / 1440 (or 1 / 96):

=MROUND(A2, 1/96)

Format the resulting formula cell as Time (Ctrl + Shift + @). For example, a punch-in time of 08:07:30 rounds to 08:15, whereas 08:06:00 rounds down to 08:00.

3. Manufacturing and Packaging Case Quantities

If products ship in bulk cartons of 12 items and customer orders must be rounded to the nearest case:

=MROUND(B2, 12)

An order of 55 items rounds to 60 units (5 full cartons), while an order of 52 items rounds to 48 units.

Handling Negative Numbers

A crucial mathematical rule in Excel’s MROUND function is that number and multiple must share the same algebraic sign:

• =MROUND(-14, -5) evaluates correctly to -15.
• =MROUND(-14, 5) triggers a #NUM! error because the signs differ.

Dynamic Sign Handling Formula

If your dataset contains a mix of positive and negative numbers, use the SIGN function to ensure the multiple always matches the sign of the input value:

=MROUND(A2, SIGN(A2) * 5)

Troubleshooting Common MROUND Errors

• #NUM! Error: Occurs when number and multiple have opposite signs. Resolve this using SIGN(A2) * multiple as shown above.
• #VALUE! Error: Displayed if either argument contains non-numeric text or improperly formatted date/time strings.
• Formatting unexpected date/times: If rounding a timestamp returns a decimal like 0.34375, the calculation is correct; simply format the cell as Time (hh:mm).