The Ins and Outs of Excel Rounding Formula
There are several Excel functions that will help you round numbers to the appropriate format. You can create formulas using these functions to get your data looking the way you want it. An Excel rounding formula allows for rounding to a specific number of decimal places, the nearest even or odd number, or the nearest multiple of a certain number. The most important functions to be used when building your Excel rounding formula are ROUND and MROUND.
ROUND, ROUNDUP, and ROUNDDOWN Functions
ROUND is a basic rounding function that allows the user to round a number to the nearest value at a specified decimal place. A formula using the ROUND function is formatted as ROUND(x,y) where x is the number to be rounded and y is the number of decimals places to round to. A y value of 0 (zero) rounds the number to the nearest integer. Positive y-values round to y digits to the right of the decimal places. Negative y-values round to y digits left of the decimal place.
For example, the formula “=ROUND(123.456,-2)” results in a value of 100 while “=ROUND(123.456,2)” results in a value of 123.46.
If you want to consistently round up, substitute ROUND for the ROUNDUP function. To always round down, use the ROUNDDOWN function instead.
MROUND Function
There are instances where you may need to round to a particular multiple like 5 or 25. To do this, employ the MROUND function in your formula. This function rounds numbers to a designated multiple in the format MROUND(x,y) where x is the number to be rounded and y is multiple to round to.
MROUND can be used with the FLOOR or CEILING functions to consistently round values down (FLOOR) or up (CEILING).
ODD and EVEN Functions
There is one more set of functions that may be helpful when creating a rounding formula. The ODD and EVEN functions round a value to nearest odd or even integer, respectively.
Experienced Excel users know that there are multiple ways to produce the same results so don’t be afraid to experiment with these functions to create the Excel rounding formula that works for you!
