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)
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 multiplied 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)
Also Read