Math function
Excel MROUND function
MROUND rounds a number to the nearest multiple of another number. =MROUND(17,5) is 15 and =MROUND(18,5) is 20. Use it for prices in steps of 5 cents, ordering in packs, or rounding clock times to the nearest 15 minutes.
=MROUND(number, multiple)Arguments
number- The number to round.
multiple- The multiple to round to, such as 5, 0.05 or "0:15". It must have the same sign as number.
MROUND examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Item | Cost | Units needed | Pack size | Clock in | |
| 2 | Bolts | 3.42 | 130 | 25 | 8:07 | |
| 3 | Washers | 1.87 | 64 | 12 | ||
| 4 | Nuts | 2.36 | 210 | 50 |
Download this sheet as an Excel practice file (.xlsx)
Result: 3.4
Rounds the cost to the nearest 5 cents: 3.42 becomes 3.4.
Result: 125
The nearest whole number of packs: 130 bolts rounds to 125, five packs of 25. That is 5 short, which is why orders usually use CEILING.
Result: 0.33333333333 shows as 8:00 with the custom h:mm format
Rounds a clock-in to the nearest quarter hour: 8:07 becomes 8:00.
Result: #NUM!
A negative number with a positive multiple returns #NUM!.
=MROUND(-7,-5)returns -5.
How to use MROUND
- Click the cell for the result.
- Type =MROUND( and the number or cell to round.
- Add a comma and the multiple, such as 5 or 0.25.
- Press Enter and fill down.
Common MROUND mistakes
Getting #NUM!
number and multiple must have the same sign. Make both negative, or round ABS(number) and put the sign back.
Expecting it to round up
MROUND goes to the nearest multiple, so 130 units in packs of 25 gives 125, which is short. Use CEILING(130,25) to always round up to 150.
Surprised by halfway values
A value exactly halfway rounds away from zero: =MROUND(7.5,5) is 10 and =MROUND(-7.5,-5) is -10.
MROUND FAQ
How do I round to the nearest 5 in Excel?
Use =MROUND(A2,5). To always round up use CEILING(A2,5); to always round down use FLOOR(A2,5).
How do I round time to the nearest 15 minutes in Excel?
Use =MROUND(A2,"0:15") and format the result as h:mm. CEILING(A2,"0:15") rounds up instead.
How do I round prices to end in .99?
=MROUND(A2,1)-0.01 rounds to the nearest dollar and takes off a cent, so 12.40 becomes 11.99 and 12.60 becomes 12.99.
Related functions
ROUNDRounds to digitsCEILINGRounds up to a multipleFLOORRounds down to a multipleROUNDUPRounds away from zero
Updated