How to round to the nearest 5 cents, 10 or 100 in Excel
ROUND works in decimal places. To round to a step, such as the nearest 0.05 or the nearest 100, use MROUND.
CEILING always rounds up to the step, and FLOOR always rounds down, which matters when running short isn’t an option.
The formula
| A | |
|---|---|
| 1 | Price |
| 2 | 12.37 |
=MROUND(A2,0.05)Result: 12.35
12.37 is closer to 12.35 than to 12.40, so that’s where it rounds.
Step by step
- Type =MROUND( and click the number.
- Add a comma and type the step, such as 0.05.
- Close the bracket and press Enter.
Other ways to write it
=ROUND(A2*20,0)/20Rounds to the nearest twentieth, which is the same as 0.05.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Using ROUND with a step
- ROUND(A2,0.05) doesn’t round to 5 cents. Its second argument is a number of decimal places.
- Rounding down when you need enough
- For packs or materials, use CEILING so you never order too few.
Reading isn’t the same as doing
Rounding to a step gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.