Excel can be very tricky about rounding numbers. Almost half of Excel users still believe that if they format a cell to round to two decimals, that what they see is what they are calculating with. This of course is not the case. For the purpose of rounding numbers in Excel three functions are available, ROUND, ROUNDUP and ROUNDDOWN. This are well known functions and I’m sure most of the readers know how to use them. But what if you want to round the values to a nearest multiple of 50? So 123 becomes 100, 12 becomes 0, 175 becomes 200 and so on… For this, we need a new set of functions.

MROUND

MROUND in an equivalent of ROUND but it rounds a number to a multiple.

=MROUND(Number,Multiple)

Samples:

=MROUND(25,50) gives you 50

=MROUND(24,50) gives you 0

Pretty straight forward, but what if you were set on rounding up to the next multiple of 50.

CEILING

CEILING in the equivalent of ROUNDUP but it rounds a number to a multiple.

=CEILING(25,50) gives you 50

=CEILING(1,50) also gives you 50

Great so far, but what if you were set on rounding down to the previous multiple of 50.

FLOOR

FLOOR in the equivalent of ROUNDDOWN but it rounds a number to a multiple.

=FLOOR(49,50) gives you 0

=FLOOR(1,50) also gives you 0