The MROUND function — round to the nearest multiple, in one call.
MROUND(number, multiple) rounds to the nearest multiple of the second argument — useful for prices (nearest $5), times (nearest 15 minutes), cash rounding (nearest $0.05), and percentage buckets (nearest 5%). Identical syntax in Excel and Google Sheets. The one gotcha: number and multiple must share a sign, or you get #NUM! — covered in §4.
#NUM! when the number and multiple have mixed signs.| Input | MROUND(x, 5)nearest | CEILING(x, 5)always up | FLOOR(x, 5)always down |
|---|---|---|---|
| 12 | 10 | 15 | 10 |
| 13 | 15 | 15 | 10 |
| 17.5 | 20 | 20 | 15 |
| 22 | 20 | 25 | 20 |
| 47.5 | 50 | 50 | 45 |
| -17 | #NUM! | -15 | -20 |
How to use MROUND
- Type
=MROUND(in the cell where you want the rounded value. - Pass the source number (cell reference or literal) as the first argument, then the multiple you want to round to.
- Close the parenthesis and press Enter.
=MROUND(A2, 5)roundsA2to the nearest 5. - For always-up or always-down rounding, use CEILING or FLOOR with the same second argument.
Mental model. Imagine the number line marked at every multiple of your second argument (0, 5, 10, 15, … for multiple=5). MROUND drops your input onto the nearest mark. Ties (the exact midpoint between two marks) snap away from zero.
MROUND syntax and arguments
Two required arguments, no optional ones. MROUND(number, multiple).
A2), a literal (12.4), or a formula that resolves to a number (SUM(B2:B5)/3). Text values produce #VALUE!.number — mixed signs return #NUM! (see §4). Common values: 5, 10, 15 (minutes), 0.05 (cents), 0.25 (quarters).| Input | Formula | Result | Why |
|---|---|---|---|
12 | =MROUND(12, 5) | 10 | 12 is closer to 10 than to 15 |
13 | =MROUND(13, 5) | 15 | Past the 12.5 midpoint → up to 15 |
17.5 | =MROUND(17.5, 5) | 20 | Exact midpoint — rounds away from zero |
43 | =MROUND(43, 15) | 45 | Closer to 45 than to 30 |
0.347 | =MROUND(0.347, 0.05) | 0.35 | Multiple can be a decimal — useful for percentages |
-17 | =MROUND(-17, 5) | #NUM! | Mixed signs — see §4 fix |
MROUND examples
Each example is the same function, different multiple — and a slightly different use case.
Example 1: round prices to the nearest $5
The textbook MROUND case. $23.7 rounds to $25; $12.4 rounds to $10. Use for “nice price” displays or pricing tiers where exact cents are noise.
Example 2: round minutes to the nearest 15
Timesheets, meeting durations, calendar slots — anything that should snap to quarter-hour increments. 43 min → 45 min; 67 min → 60 min.
Example 3: cash rounding to nearest $0.05
When pennies are phased out (Canada, parts of Europe), tills round totals to nickel multiples. 4.97 → 4.95; 4.98 → 5.00 (midpoint, away from zero). The multiple is a decimal — MROUND handles that natively.
Example 4: percentages to the nearest 5%
Survey-result rounding, conversion-rate bucketing — anywhere the third decimal is over-precise. 34.7% rounds to 35%. The multiple 0.05 works because percentages are stored as decimals (5% = 0.05).
Common MROUND errors and fixes
One headline error to know about (mixed signs), and two minor variants on input handling.
MROUND returns #NUM! on a negative number
Cause: Excel's MROUND requires number and multiple to share a sign. =MROUND(-17, 5) → #NUM!. Excel won't guess which direction is “up” for a negative.
Fix 1 — flip the multiple sign: =MROUND(-17, -5) returns -15.
Fix 2 — divide-trick: =ROUND(A1/5, 0) * 5 handles any input, positive or negative, without the restriction.
Fix 3 — conditional sign: =MROUND(A1, IF(A1<0, -5, 5)) picks the right sign automatically.
MROUND returns #VALUE!
Cause: number is text that Excel can't coerce to a number — usually a string with a currency symbol ("$5.50") or a comma thousands separator stored as text.
Fix: wrap in VALUE() or clean the source — =MROUND(VALUE(A1), 5). The standalone digits like "5.50" usually auto-coerce; the symbols are what break it.
Result rounds the “wrong” way at exact midpoints
Cause: MROUND uses half-away-from-zero for ties — =MROUND(2.5, 5) returns 5, not 0. This matches ROUND's behaviour but surprises users expecting banker's rounding (half to even).
Fix: there's no banker's flag for MROUND. If banker's rounding to a multiple is required, you need a custom formula combining MOD, ROUND, and an explicit half-tie check.
MROUND vs CEILING, FLOOR & ROUND
Four functions, one shape of problem. Pick by direction and whether negatives are involved.
| Function | Direction | Handles negatives? | Typical use |
|---|---|---|---|
| MROUND | Nearest (ties away from zero) | Same-sign multiple required | “Round to nearest 5/15/0.05” |
| CEILING | Always away from zero (up for positives) | Yes — no restriction | Pricing up to nice $, pack-size minimum |
| FLOOR | Always toward zero (down for positives) | Yes — no restriction | Binning (ages 20-24 → 20), conservative quotes |
| ROUND | Nearest (decimals, not multiples) | Yes — works on any sign | Round to 2 decimals; combined with /n*n trick for multiples |
Rule of thumb: MROUND for nearest, CEILING for up, FLOOR for down, ROUND with the divide-trick (=ROUND(A/5,0)*5) when MROUND's sign restriction blocks you. See the nearest-5 deep dive for a worked four-method comparison.
MROUND frequently asked questions
6.01What does MROUND do in Excel?▸
MROUND(number, multiple) rounds the first argument to the nearest multiple of the second. =MROUND(12, 5) returns 10 (12 is closer to 10 than to 15). =MROUND(13, 5) returns 15. =MROUND(17.5, 5) returns 20 — on the exact midpoint, MROUND rounds away from zero. The cleanest way to round to nearest $5, nearest 15 minutes, nearest 5%, or any other multiple in both Excel and Google Sheets.
6.02Why does MROUND return #NUM! on negative numbers?▸
Excel's MROUND requires the number and the multiple to share a sign. =MROUND(-17, 5) returns #NUM! because -17 is negative but 5 is positive. Workarounds: flip the multiple's sign — =MROUND(-17, -5) returns -15 — or use the divide-trick =ROUND(A1/5, 0) * 5 which has no sign restriction. CEILING and FLOOR also work on negatives without issue.
6.03What's the difference between MROUND and ROUND?▸
ROUND rounds to a number of decimals (place value): =ROUND(12.345, 2) → 12.35. MROUND rounds to a multiple (unit step): =MROUND(12.345, 0.05) → 12.35. They overlap for power-of-10 multiples — =MROUND(123, 10) and =ROUND(123, -1) both return 120. For arbitrary multiples like 5, 15, or 0.25, MROUND is the natural fit; ROUND only matches with the divide-trick =ROUND(A/multiple, 0) * multiple.
6.04Can MROUND round up or down only?▸
No — MROUND always picks the nearest multiple. For always-up to a multiple, use CEILING(number, multiple). For always-down, use FLOOR(number, multiple). The three together cover every rounding-to-multiple need.
6.05Does MROUND work in Google Sheets?▸
Yes — Google Sheets' MROUND has identical syntax and behaviour to Excel, including the same #NUM! mixed-sign error. =MROUND(A1, 5) works the same on both platforms. CEILING, FLOOR, and the ROUND divide-trick also work identically.
6.06Does MROUND use banker's rounding for .5 ties?▸
No. MROUND ties on the exact midpoint round AWAY from zero (same as ROUND): =MROUND(2.5, 5) returns 5, =MROUND(-2.5, -5) returns -5. There's no banker's-rounding flag for MROUND. If you need banker's rounding to a multiple, write a custom formula combining MOD, ROUND, and an explicit half-tie check — see the rounding modes deep dive.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.