Rounding to the Nearest Multiple (Excel 2010 Training)

Round Numbers To The Nearest Multiple

If you need to round numbers to some specified increment, such as rounding prices to the nearest 25¢, the MROUND function in Excel can help you.

Round values in Microsoft 2000 - 2003 Excel to the nearest multipleFunction Syntax:

=MROUND(number,multiple)

The MROUND function will round a number to the nearest multiple of the second argument. For example, in this store all retail prices are multiples of 25¢ . To calculate the prices we can use this MROUND formula.

=MROUND(C4,0.25)

Keep in mind that in order for the MROUND function to work, both arguments in the function must have the same sign. Therefore, if cells referenced by MROUND have a mix of positive and negative amounts, you will need to multiply the second argument by the sign of the first argument using the SIGN function.

In the above example, =MROUND(C4,0.25*SIGN(C4)) will work for both positive and negative values. The SIGN function returns a 1 if the sign is positive and -1 if the sign is negative.