Use-case deep dive · Excel & Google Sheets

Round to the nearest 5 in Excel — four methods, one default.

Q: How do I round to the nearest 5 in Excel?

Use =MROUND(A1, 5) — it rounds A1 to the nearest multiple of 5. For example, MROUND(12, 5) returns 10, MROUND(13, 5) returns 15, and MROUND(17.5, 5) returns 20 (Excel rounds .5 ties away from zero). MROUND is the most direct answer when you want "nearest". If you specifically need to round UP to the next 5 use CEILING(A1, 5); for round-DOWN use FLOOR(A1, 5).

Q: What's the difference between MROUND, CEILING, and FLOOR for rounding to 5?

MROUND(x, 5) picks the nearest multiple — direction depends on which side of the midpoint x falls. CEILING(x, 5) always rounds AWAY from zero (toward the next higher multiple for positive numbers). FLOOR(x, 5) always rounds TOWARD zero (toward the next lower multiple for positive numbers). For 12: MROUND→10, CEILING→15, FLOOR→10. For 13: MROUND→15, CEILING→15, FLOOR→10. Pick by intent: "nearest", "never less than", or "never more than".

Q: Why does MROUND return #NUM! for negative numbers?

Excel's MROUND requires the number and the multiple to have the same sign. =MROUND(-17, 5) returns #NUM! because -17 is negative but 5 is positive. Two workarounds: (1) flip the multiple's sign: =MROUND(-17, -5) returns -15. (2) Use the divide-trick instead: =ROUND(A1/5, 0) * 5 handles negatives without errors. CEILING and FLOOR also work on negatives without issue.

Q: Can I use the ROUND function to round to nearest 5?

Yes, with the divide-trick: =ROUND(A1/5, 0) * 5. Divide by 5, round to whole, multiply by 5. The result matches MROUND for positive numbers, and unlike MROUND it doesn't error on negatives. The downside is the formula is less self-documenting — a reader sees "ROUND, divide, multiply" instead of "round to nearest 5". Use MROUND when you can; fall back to the divide-trick for negatives or when ROUND is the only function you remember.

Q: How do I round to nearest 10 / 25 / 0.5 in Excel?

Same pattern, different multiple. =MROUND(A1, 10) rounds to nearest 10, =MROUND(A1, 25) to nearest 25, =MROUND(A1, 0.5) to nearest half. The divide-trick variant: =ROUND(A1/10, 0)*10, =ROUND(A1/25, 0)*25, =ROUND(A1/0.5, 0)*0.5. CEILING and FLOOR take the same 2nd argument: CEILING(A1, 10), FLOOR(A1, 25), etc. The pattern works for any positive multiple.

Q: Does "round to nearest 5" work the same in Google Sheets?

Yes. Google Sheets has MROUND, CEILING, and FLOOR with identical syntax and behaviour, including the same MROUND mixed-sign #NUM! error. Every formula on this page works on both platforms unchanged. One small difference: Sheets has CEILING.MATH and FLOOR.MATH (mirroring Excel 2013+) for more control over how negatives behave, with the same arguments.

Last updated: June 2026

The short answer is =MROUND(A1, 5) — it returns the nearest multiple of 5 (with a small negative-number caveat below). But three other formulas accomplish the same family of tasks with slightly different rounding direction: CEILING(A1, 5) always rounds up, FLOOR(A1, 5) always rounds down, and the divide-trick =ROUND(A1/5, 0) * 5 matches MROUND and handles negatives without errors. The interactive comparison below shows all four side by side; the rest of the page is when to reach for which, plus the same pattern generalised to nearest 10, 25, or 0.5.

Round to nearest 5Same input on every row — see how each method handles it. MROUND is the canonical “nearest” (with one negative-number gotcha).

Input	`MROUND(x, 5)`nearest	`CEILING(x, 5)`always up	`FLOOR(x, 5)`always down	`ROUND(x/5,0)*5`divide-trick
12	10	15	10	10
13	15	15	10	15
17.5	20	20	15	20
22	20	25	20	20
23	25	25	20	25
47	45	50	45	45
-17	#NUM!	-15	-20	-15

12Just over 10 — nearest is 10

13Past the .5 midpoint of 12.5 → 15

17.5Exactly on the midpoint of 15 and 20

-17Negative input — MROUND errors

You want	Reach for	Why
Nearest multiple of 5 (default)	`=MROUND(A1, 5)`	Reads exactly like the intent — one function call
Always round UP to a multiple of 5	`=CEILING(A1, 5)`	For pricing “up to nearest $5” or pack sizes
Always round DOWN to a multiple of 5	`=FLOOR(A1, 5)`	For grouping into bins where overflow rolls into the next bin
Nearest 5, but the input can be negative	`=ROUND(A1/5, 0) * 5`	MROUND errors on negatives; the divide-trick doesn't

01 · The four functions

Four methods — same destination, different routes

Each handles “round to nearest 5” for the common case. They diverge on direction control and negative numbers.

MROUND — nearest multiple, plain English

=MROUND(number, multiple) picks the multiple closest to number. Ties (the exact midpoint between two multiples) round away from zero: 12.5 becomes 15, -12.5 becomes -15. This is the function that reads most directly as “nearest 5”.

=MROUND(13, 5) → 15
=MROUND(12, 5) → 10
=MROUND(17.5, 5) → 20 (midpoint rounds away from zero)

CEILING — always round up

=CEILING(number, multiple) rounds away from zero to the next multiple — never landing below the input. The classic pricing-up use case: =CEILING(23.4, 5) returns 25, snapping a price point up to the next “nice” number.

=CEILING(12, 5) → 15
=CEILING(15, 5) → 15 (already a multiple, no change)
=CEILING(22, 5) → 25

FLOOR — always round down

=FLOOR(number, multiple) rounds toward zero — never landing above the input. Useful for binning ranges: ages 20–24 all land on 20, 25–29 all land on 25.

=FLOOR(13, 5) → 10
=FLOOR(17, 5) → 15
=FLOOR(47, 5) → 45

The divide-trick — only ROUND on hand

=ROUND(A1 / 5, 0) * 5 achieves the same answer as MROUND for the common case. Divide by 5 (shrinking the number to fit a unit scale), ROUND to a whole, multiply by 5 to scale back. Useful when you only remember ROUND, when you need to round negatives (MROUND errors on those), or when your spreadsheet locale lacks MROUND on certain Excel versions.

=ROUND(13/5, 0) * 5 → ROUND(2.6, 0) * 5 → 3 * 5 → 15

02 · The negative-number gotcha

MROUND's mixed-sign rule

Excel's MROUND requires the number and the multiple to share a sign. Mix signs and you get #NUM!.

=MROUND(-17, 5) returns #NUM! — not because the math is impossible, but because Excel enforces a sign-match between the two arguments. The undocumented intent is to prevent ambiguity about which direction is “up” for negative numbers.

Workaround 1 — flip the multiple=MROUND(-17, -5) → -15. Same result you'd expect.

Workaround 2 — use the divide-trick=ROUND(-17 / 5, 0) * 5 → -15. No sign-match restriction; works for any input.

Workaround 3 — use ABS to detect intent=MROUND(A1, IF(A1 < 0, -5, 5)) — programmatically flips the sign. Verbose but explicit.

CEILING and FLOOR don't have this restriction. =CEILING(-17, 5) returns -15 (toward zero) and =FLOOR(-17, 5) returns -20 (away from zero). If you regularly handle negatives, prefer those two or the divide-trick.

03 · The same pattern, other multiples

Nearest 10, nearest 25, nearest 0.5

Replace 5 in any formula with the multiple you want — the pattern is identical for nearest 10, 25, 100, 0.5, or 0.25.

Goal	MROUND	Divide-trick
Nearest 10	`=MROUND(A1, 10)`	`=ROUND(A1/10, 0) * 10`
Nearest 25	`=MROUND(A1, 25)`	`=ROUND(A1/25, 0) * 25`
Nearest 100	`=MROUND(A1, 100)`	`=ROUND(A1/100, 0) * 100`
Nearest 0.5 (half)	`=MROUND(A1, 0.5)`	`=ROUND(A1 * 2, 0) / 2`
Nearest 0.25 (quarter)	`=MROUND(A1, 0.25)`	`=ROUND(A1 * 4, 0) / 4`
Nearest 0.05 (cent — for currency-rounding from fractions)	`=MROUND(A1, 0.05)`	`=ROUND(A1 * 20, 0) / 20`

For nearest fractions like 0.5 or 0.25, the divide-trick takes the reciprocal shape (multiply first, divide after) — same algebra, easier to spot the unit.

04 · Where this actually shows up

Real-world use cases

Six common scenarios that hit “round to nearest 5” — and which function each maps to.

Scenario	Use this	Example
Pricing to “nice” $5 increments	`CEILING` — never under-price	`=CEILING(23.4, 5)` → `25`
Timesheet rounding to nearest 5 minutes	`MROUND` on minutes	`=MROUND(minutes, 5)` → 43 → 45
Age binning (20-24 = bin 20, 25-29 = bin 25)	`FLOOR` — bin always rounds down	`=FLOOR(23, 5)` → `20`
Pack-size minimum order (5/pack, order at least N)	`CEILING` — round up to full pack	`=CEILING(12, 5)` → `15` (3 packs)
Snap survey ratings to 5-point intervals	`MROUND`	`=MROUND(72, 5)` → `70`
Round percentages to nearest 5%	`MROUND` with multiple `0.05`	`=MROUND(0.347, 0.05)` → `0.35` (35%)

05 · Where each runs

Excel & Sheets compatibility

Every function on this page works the same way across modern Excel, older Excel, and Google Sheets.

Function	Excel 365 / 2021	Excel 2010+	Google Sheets
`MROUND`	✅	✅ (since 2007 via Analysis Toolpak; native since 2010)	✅
`CEILING` / `CEILING.MATH`	✅	✅ (CEILING since Excel 97; .MATH since 2013)	✅
`FLOOR` / `FLOOR.MATH`	✅	✅ (FLOOR since Excel 97; .MATH since 2013)	✅
`ROUND(x/5, 0) * 5` divide-trick	✅	✅ (ROUND is universal)	✅

The ROUND main reference and adjacent rounding deep dives.

06 · Quick answers

FAQ

6.01How do I round to the nearest 5 in Excel?▸

Use =MROUND(A1, 5) — it rounds A1 to the nearest multiple of 5. For example, MROUND(12, 5) returns 10, MROUND(13, 5) returns 15, and MROUND(17.5, 5) returns 20 (Excel rounds .5 ties away from zero). If you specifically need to round UP to the next 5 use CEILING(A1, 5); for round-DOWN use FLOOR(A1, 5).

6.02What's the difference between MROUND, CEILING, and FLOOR for rounding to 5?▸

MROUND(x, 5) picks the nearest multiple — direction depends on which side of the midpoint x falls. CEILING(x, 5) always rounds AWAY from zero (toward the next higher multiple for positive numbers). FLOOR(x, 5) always rounds TOWARD zero (toward the next lower multiple for positive numbers). For 12: MROUND→10, CEILING→15, FLOOR→10. For 13: MROUND→15, CEILING→15, FLOOR→10. Pick by intent: “nearest”, “never less”, or “never more”.

6.03Why does MROUND return #NUM! for negative numbers?▸

Excel's MROUND requires the number and the multiple to have the same sign. =MROUND(-17, 5) returns #NUM! because -17 is negative but 5 is positive. Two workarounds: (1) flip the multiple's sign: =MROUND(-17, -5) returns -15. (2) Use the divide-trick instead: =ROUND(A1/5, 0) * 5 handles negatives without errors. CEILING and FLOOR also work on negatives without issue.

6.04Can I use the ROUND function to round to nearest 5?▸

Yes, with the divide-trick: =ROUND(A1/5, 0) * 5. Divide by 5, round to whole, multiply by 5. The result matches MROUND for positive numbers, and unlike MROUND it doesn't error on negatives. The downside is the formula is less self-documenting — a reader sees “ROUND, divide, multiply” instead of “round to nearest 5”. Use MROUND when you can; fall back to the divide-trick for negatives or when ROUND is the only function you remember.

6.05How do I round to nearest 10 / 25 / 0.5 in Excel?▸

Same pattern, different multiple. =MROUND(A1, 10) rounds to nearest 10, =MROUND(A1, 25) to nearest 25, =MROUND(A1, 0.5) to nearest half. The divide-trick variants: =ROUND(A1/10, 0)*10, =ROUND(A1/25, 0)*25, =ROUND(A1*2, 0)/2. CEILING and FLOOR take the same 2nd argument: CEILING(A1, 10), FLOOR(A1, 25), etc.

6.06Does “round to nearest 5” work the same in Google Sheets?▸

Yes. Google Sheets has MROUND, CEILING, and FLOOR with identical syntax and behaviour, including the same MROUND mixed-sign #NUM! error. Every formula on this page works on both platforms unchanged. One small difference: Sheets has CEILING.MATH and FLOOR.MATH (mirroring Excel 2013+) for more control over how negatives behave, with the same arguments.