Excel MROUND function is perfect for rounding numbers to the nearest multiple.
For example, last week Brenda said she was making a timesheet and wanted to round the start and finish times to the nearest 15 minutes while allowing for a 7 minute grace period. The table below has some examples:
Rounding these times to the nearest 15 minutes is easy with the MROUND function, like so:
Take note of the double quotes around the 0:15 in the formula above. More on that in a moment.
Warning, boring stuff ahead.
Excel MROUND Function Syntax
MROUND is available in Excel 2007 onwards.
The syntax for the MROUND function is:
Where number is the cell or value you want to round, and multiple is the multiple you want to round your number to.
MROUND rounds up away from zero if the remainder left after dividing your number by the multiple is greater than or equal to half the value of multiple. What!?
Sorry, but I warned you it was boring 😉 Let's look at some examples as it will make more sense....hopefully.
Rounding Numbers with MROUND
When working with time the multiple is entered in a time format "h:mm" surrounded by double quotes, as you can see in the first example above, which was:
However, when using MROUND to round regular numbers you simply enter the multiple without the double quotes. For example; let’s say we have some prices that we want to round to the nearest dollar as shown below:
Or maybe you're pricing products and want to round to the nearest 5 cents:
Rounding negative values requires you to use the same sign in the ‘multiple’ argument otherwise you get a #NUM! error:
MROUND will round up or down to the nearest multiple, but if you want to fix the direction of rounding you can use: