MROUND() is lesser known but really useful rounding function in Excel, it’s worth considering for anyone dealing with cash money.
In short: MRound() rounds up or down to the nearest multiple you choose. It’s very handy for rounding prices to match the lowest coin or note you want to handle, but it’ll have other uses too.
= MROUND(number, num_digits)
It’s like the Round() function, except you’ll round a number to the specific multiple, e.g. round to the nearest 0.5. MROUND will round up or down (away from zero) if the remainder of dividing number by multiple is greater than or equal to half the value of multiple.
Mround() has been in Excel since at least Excel 2007 for Windows and Excel 2011 for Mac.
Mround() with cash money
MRound() is very useful for prices in “old-fashioned” 😀 physical money where there’s a lowest denomination coin or your business wants to reduce handling of small denomination coins.
For example in New Zealand the smallest coin is 10 cents so you’d use Mround(????,0.10) to get a price that is divisible by 0.10
In Australia the smallest coin is 5 cents, so you’d use Mround(????,0.05) to get a price that is divisible by 0.05 . Canada is also working to eliminate their penny and have a 5 cent coin minimum.
There’s been talk in both Australia and New Zealand about eliminating of 10 cent coins meaning you’d need Mround(????,0.2). 20 cents is the next highest coin.
In the USA, perhaps your business would like to reduce cash handling by making all prices divide by 25 cents (a quarter) with Mround(????,0.25)
Even in countries that still have pennies (US and UK), some stores might prefer to have their prices in round numbers to avoid messing with smaller coins.
Always rounding up?
MRound() always rounds to the nearest multiple, see the two ‘dollar’ examples above.
What if you always want to round up so that even $8.01 rounding to a dollar gives $9 (not $8 as Mround() will do.
To do that, you need the Ceiling() function.
Excel’s Round () and five other rounding functions
Excel’s Trunc() Function
How Microsoft left an Outlook security hole that’s way too easy for hackers