Monday, May 25, 2009

Round to the Nearest Multiple Other Than 10

If you want to round a price to nearest nickel (multiple of 0.05), or a length to the nearest inch (multiple of 1/12), or a number of minutes to the nearest second (multiple of 1/60), you can use the following formula (where mult is the multiple to round to):

=ROUND(number/mult,0)*mult
or just
=MROUND(number,mult)

(Thanks to mmmoj's comment for the MROUND function)

If you want to round UP or DOWN, you can use ROUNDUP or ROUNDDOWN instead, or the CEILING or FLOOR functions:
=ROUNDUP(number/mult,0)*mult
=CEILING(number,mult)

=ROUNDDOWN(number/mult,0)*mult
=FLOOR(number,mult)

The ROUND, ROUNDUP, and ROUNDDOWN functions will round negative numbers as if you first multiplied the value by -1, did the rounding, and then multipled by -1 again. So -23.6 rounded UP would be -24. When using CEILING or FLOOR, the multiple needs to be the same sign as the number.

Round Price to Nearest Nickel
=ROUND(price/0.05,0)*0.05
=MROUND(price,0.05)

=CEILING(price,0.05)    (rounds up)

Round Minutes to the Nearest Second
=ROUND(minutes/(1/60),0)*(1/60)
=MROUND(minutes,(1/60))

Round Feet to the Nearest Inch
=ROUND(feet/(1/12),0)*(1/12)
=MROUND(feet,(1/12))

Round to the Nearest Even Number
=ROUND(number/2,0)*2
=EVEN(number)    (rounds up)

Round to the Nearest Integer
=ROUND(number,0)

=INT(number)    (rounds down)

Round to the Nearest 15-Minute Interval
=ROUND(minutes/15,0)*15

=CEILING(minutes,15)   (rounds up)

9 comments :

Unknown said...

Instead of your rounding formula, you should just use MROUND. For example, to round 87 to the nearest multiple of 12, you would write =MROUND(87,12), and the answer would be 84.

The 'M' is probably for "Multiple."

Jon Wittwer said...

And to think all this time I've been doing it the hard way!!! Thank you for enlightening me. I'll add this to the top of the post, but leave the rest of the formulas as-is.

WanYgBest said...

hi...thank you so much for your tips, but I wonder how to round the number to the nearest 5? if i have a set of data e.g (-1, -7, 4, 14), how do i change it to (0,-10,5, 15)?

hope to hearing from you soon. Thanks!

Jon Wittwer said...

WanYgBest, use the following formula:
=ROUND(num/5,0)*5
where num=-1 or num=-7, etc.

Umapathy said...

thanks for the tips.

aschne said...

Help?!?

I am using the following formula to try and get it to round to the nearest 12.
ROUND(Num/12,0)*12

It works for every number except 0...any thoughts?

THANKS!

Anonymous said...

mine is telling me I'm making circular references. I need the formula to stay in the cell so that when I input my test value (say I get 5 mm and I need it to always round to the closest 3). Is there a way to keep this MROUND formula in the cell you are inputting data in?

Anonymous said...

aschne:

=ROUND(NUM,-1)

Anonymous said...

How do you include a number of thresholds, ie, below 10, 15, 20, 30, 45 and 60 mins?