Rounding values to a specific decimal value

[Eberle]

I'm currently working with pricing data, and so I want all my values to rounded up to the nearest .5 or nearest .9.  Likewise, I want to also round all my values down to the nearest .5 or .9 (whichever comes first).  For example if I had the values 3.54, 4.57, and 5.31, I would want them to round up to 3.55, 4.59, 5.35 and round down to 3.49, 4.55, and 5.29. What is the best way to go about doing this?

[Eberle]

Sorry that should say the nearest .09 or nearest .05

[danrh · Accepted solution]

This seems like a not-very-straight-forward way to do this,but give these a try in a Formula tool:

Round Down:

[Price]-
IF Mod([Price]*100, 10)/100<.05 
	THEN Mod([Price]*100, 10)/100+.01
ELSEIF Mod([Price]*100, 10)/100=.09 THEN 0
ELSE Mod([Price]*100, 10)/100-.05 ENDIF

Round Up:

FLOOR([Price]*10)/10+
IF Mod([Price]*100, 10)/100>.05 THEN .09
ELSE .05 ENDIF

[Eberle]

The round down formula returned a few incorrect values that ended in 0.06, but the round up formula worked perfectly! I probably just need to tweak one or two things with the round down formula.  Thanks for the help!

[Eberle · Accepted solution]

FLOOR([Median_menu_price]*10)/10 +
IF Mod([Median_menu_price]*100, 10)/100>=.08 THEN .09
ELSEIF Mod([Median_menu_price]*100, 10)/100>=.05 THEN .05
ELSE -0.01 ENDIF

Here's what the solution for the round down portion should be.