The CEILING, FLOOR, MROUND guide
=CEILING(B2, 15) rounds up to the next multiple of 15, =FLOOR(B2, 15) down, and =MROUND(B2, 15) to the nearest. The second argument is the step you round to: 5, 15, 0.25, 0.05.
Three directions, one step
The same minutes, rounded to a step of 15:
| Minutes | CEILING | MROUND | FLOOR |
|---|---|---|---|
| 31 | 45 | 30 | 30 |
| 44 | 45 | 45 | 30 |
| 30 | 30 | 30 | 30 |
A value that is already a multiple stays as it is with all three.
Which one?
- CEILING when every started block counts: billing per started 15 minutes, parking per started hour, packs of 6.
- MROUND when the nearest step is fair: a timesheet to the nearest quarter hour, a price to the nearest 0.05. Halfway goes up:
MROUND(7.5, 5)is 10. - FLOOR when only complete steps count: full hours worked, whole crates of 24.
Minutes to quarter hours
A quarter of an hour is 0.25, so divide the minutes by 60 and round to 0.25: =MROUND(B2/60, 0.25). 52 minutes is 0.867 hours, which goes to 0.75. If the cells hold real times (0:52) rather than minutes, Excel also accepts a time as the step: =MROUND(A2, "0:15").
Signs and versions
- MROUND needs the number and the step to have the same sign;
MROUND(5, -2)returns #NUM!. CEILING.MATHandFLOOR.MATHare the newer versions. The step is optional (1 if you leave it out) and its sign doesn't matter.- ROUND can only go to decimal places, tens or hundreds. It can't round to 15 or 0.25.
Common mistakes
- MROUND for billing. A 31-minute call goes to 30, so the client doesn't pay for the started block.
- Rounding minutes as if they were hours.
MROUND(B2, 0.25)on 52 minutes gives 52. - Unlocked settings. Lock the step cells,
$G$1and$G$2, before you fill down.
The step before this one: ROUNDUP and ROUNDDOWN.
FAQ
- How do I round to the nearest 15 minutes in Excel?
- For minutes as numbers use =MROUND(A2,15). For real times use =MROUND(A2,"0:15"). Use CEILING instead to always round up.
- How do I round to the nearest 5 or 0.05 in Excel?
- Use MROUND with the step: =MROUND(A2,5) or =MROUND(A2,0.05).
- What is the difference between CEILING and MROUND?
- CEILING always rounds up to the next multiple. MROUND goes to the nearest multiple, up or down.
- Why does MROUND return #NUM!?
- The number and the multiple have different signs, such as MROUND(5,-2).



































































