*mult*is the multiple to round to):

=ROUND(number/mult,0)*multor 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 :

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."

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.

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!

WanYgBest, use the following formula:

=ROUND(num/5,0)*5

where num=-1 or num=-7, etc.

thanks for the tips.

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!

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?

aschne:

=ROUND(NUM,-1)

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

Post a Comment