In business, we often leverage data from multiple systems, ultimately wanting the numbers from each system to reconcile perfectly.  Yet sometimes…that isn’t the case.  While you may only be off by a few pennies, for reporting you still want to get the difference to zero.

Excel offers at least ten different rounding formulas, but we’ll review a few here today.

First, let’s review how changing the cell format can visibly show you a rounded number by adjusting the decimal places.  Say you have a value in your spreadsheet of 19.4688.  You right-click on that cell, click on the “Format Cells” option, select “Number” and “2 Decimal Places”. This makes your 19.4688 number appear to be 19.47 – however, in all formulas the full number is still used.  So, this looks pretty but doesn’t really round the number for calculation purposes.

Now let’s check out the round ROUND function. This does exactly what you think it would, it rounds the number to how many digits you determine.  In the prior example of 19.4688, we could enter the following function =ROUND(A1,2) [A1 is the cell your number is located in and “2” represents the number of digits to round the number to] and Excel would return a value of 19.47.  If the following was entered, =ROUND(A1,0) it would return 19.00.

The MROUND function is also something I use a lot.  This function rounds the number up or down to a desired multiple.  For example, let’s say you want all employee time rounded to the nearest quarter of an hour.  Using the formula, =MROUND(A1,0.25) will take the 19.2458 number and return a value of 19.25. If you wanted to take it instead to the nearest half hour, you could enter =MROUND(A1,0.50).  See examples below:

What if you have a situation in which you want to do something similar to the MROUND function, but you always want to either round up or round down?  The two functions CEILING and FLOOR now become your best friends!  Taking the numbers from the MROUND example and putting them into a FLOOR and CEILING results in the below.

This concludes this totally fascinating overview of four simple rounding functions used in Excel, but remember, there are at least 6 more out there to discover! I have historically found these most helpful and its my hope that one of these can help save you some time and headache in trying to find those last few pennies of a difference!

Sandy Noble, Onboarding Controller