If you use excel enough you’ll notice that after you make a decently complicated calculation, excel gives you the result in its entirety no matter how many decimals the result contains. The thing is, you don’t usually notice it until you’re dealing with a large amount of data and the totals don’t quite add up. Rounding differences are something that most of us deal with, but excel provides a few useful rounding functions to help you in various situations.
In case you’re not sure what I mean by rounding differences and how excel inherently causes them, here is an example:
Lets do some basic arithmetic. 933 divided by 7 equals 133.29; right? If you are applying standard formatting (like accounting or number) to the cell, excel might just display the number rounded by two decimal places:
Don’t judge a book by its cover though; the actual value that excel stores in this cell is much more precise.
Don’t worry, excel has been lying to us all this whole time. If you’re adding thousands of these numbers; those tiny decimal values begin to add up and will cause you to be off! One thing you could do that might help get around this is you can instruct excel to round the numbers after a certain point using ROUND:
ROUND – Rounds digits either up or down using the standard rules (ie. = or > 5 round up, < 4 round down).
ROUNDUP – Rounds digits up to the nearest place
ROUNDDOWN – Rounds digits down to the nearest place
MROUND – Rounds digits either up or down to the nearest multiple, depending on which multiple you select.
Lets take a closer look at the function’s syntax:
=ROUND/ROUNDDOWN/ROUNDUP(Number, Number of digits)
Number – Any real number you want to be rounded
Number of Digits – The number of digits after or before the decimal place to which you want to the number rounded
Lets Get Practical
=MROUND(number, multiple)
Number – Any real number you want to be rounded
Multiple – The multiple to which you want to round number.
