How to / Percentages and rounding
How to round to the nearest 5 in Excel
Use =MROUND(A2, 5). It rounds to the nearest multiple of the second number, up or down, whichever is closer.
To the nearest 5
| A | B | C | |
|---|---|---|---|
| 1 | Value | ||
| 2 | 23 | 25 | |
| 3 | 4.42 | ||
| 4 | 47 | ||
| 5 | 1234 |
In C2: =MROUND(A2, 5) → 25
23 is closer to 25 than to 20.
How it works
- MROUND finds the multiples of 5 on either side of the number and returns the nearer one.
- A number exactly halfway, such as 22.5, goes up.
- The second argument can be any step: 10, 100, 0.5 or 0.05.
Variations
Always up to the next 5
A B C 1 Value 2 23 25 3 4.42 4 47 5 1234 In C2:
=CEILING(A2, 5)→ 25Always down to the 5 below
A B C 1 Value 2 23 20 3 4.42 4 47 5 1234 In C2:
=FLOOR(A2, 5)→ 20To the nearest 0.5
A B C 1 Value 2 23 4.5 3 4.42 4 47 5 1234 In C2:
=MROUND(A3, 0.5)→ 4.5To the nearest 10
A B C 1 Value 2 23 50 3 4.42 4 47 5 1234 In C2:
=MROUND(A4, 10)→ 50To the nearest 100
A B C 1 Value 2 23 1200 3 4.42 4 47 5 1234 In C2:
=ROUND(A5, -2)→ 1200For tens, hundreds and thousands, ROUND with a negative number of digits does the same job.
Watch out for
- #NUM! with negative numbers. The number and the multiple must have the same sign. For -23, write
=MROUND(-23, -5). - Tiny differences with decimals. Steps such as 0.05 cannot be stored exactly, so a result may show as 4.4 but differ from 4.4 far down the decimals. Wrap it in ROUND if you compare results:
=ROUND(MROUND(A3, 0.05), 2).
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- MROUND: Rounds to the nearest multiple of a number.
- CEILING: Rounds a number up to the nearest multiple (away from zero when both are negative).
- FLOOR: Rounds a number down to the nearest multiple (toward zero when both are negative).
- ROUND: Rounds a number to a given number of digits.
- ROUNDUP: Rounds a number away from zero.
- ROUNDDOWN: Rounds a number towards zero.