Rounding to the nearest quarter

Posted by Franklin on November 07, 2001 10:22 AM

This might be very simplistic, but I was wondering what would be the best way to round formula results to the nearest .25,.50,.75,.00 (either way, depending-- .12 would go down to 0, whereas .13 would go to .25.

Any help would be great.

Posted by IML on November 07, 2001 10:31 AM

assuming a value in A1, you could use
=MROUND(A1,0.25)
you need to install analysis tool pak to activate this functions.

As Aladin pointed out yesterday, you could avoid this by using the following formula with no add-ins
=ROUND((A1)/0.25,0)*0.25

Posted by Franklin on November 07, 2001 10:55 AM

Thanks... that worked on a single cell. Perhaps you would also know how to in corporate that in an "if" formula. It does not appear to work within a formula set like If a2 = "yes",=round(a1 ..etc) Any thoughts?

Posted by IML on November 07, 2001 11:53 AM

You could use
=IF(A2="yes",ROUND((A1)/0.25,0)*0.25,A1) if you want to round A1 if A2 = yes, otherwise it will just return the value.
You could also change the last argument to "".

Or if you're like me always mixing up rows and columns, maybe you really want
=IF(B1="yes",ROUND((A1)/0.25,0)*0.25,A1)

good luck

Posted by Franklin on November 07, 2001 12:46 PM

Thanks...