Rounding to nearest 1/4 (Excel 2007)

Topic

New Reply

I need to round to the nearest 1/4. For example, 10.37, would round up to 10.5 and 10.81 would round down to 10.75. I know how to use roundup, rounddown, and round. None of those would do the trick. Ceiling and floor don’t quite work out either. Perhaps a combination of ceiling and floor, but is there anything simpler? Thanks

Reply | Quote

Replies

(Edited by mbarron on 11-Dec-08 13:48. typo)

Try the Mround() function:
=MROUND(A1,0.25)

Reply | Quote

Thanks. Exactly what I was looking for. Why doesn’t the help file do a related reference in ceiling? That’s where I started looking. It’s great to have Woody’s Loungers around!

Reply | Quote

It may not show in the “Related” because the MROUND function is part of the Analysis Toolpak add in.

Reply | Quote

Hi Cathy,

You could also use:
=ROUND(A1*4,)/4
That avoids the need for the users to have the Analysis Toolpack installed. Also, according to your specs, 10.37 should round to 10.25 (which is what the above formula does), not 10.50.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Even Better! Thanks

Reply | Quote

A GEILING/FLOOR mixed formula, may meet your expected result

That is, 10.37 round up to 10.5 , and 10.81 round down to 10.75

Try the formula……

=IF(MOD(A1,10)>=0.5,FLOOR(A1,0.25),CEILING(A1,0.25))

A testing at the range of value, from 10.01 to 10.99,
below table show the formula results arising from MROUND and GEILING/FLOOR

Range of value….MROUND…GEILING/FLOOR
10.01 to 10.12……..10.00……10.25
10.13 to 10.25……..10.25……10.25
10.26 to 10.62……..10.25……10.50
10.63 to 10.74……..10.75……10.50
10.75 to 10.99……..11.00……10.75

Regards
Bosco

Reply | Quote