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?
Solved! Go to Solution.
Sorry that should say the nearest .09 or nearest .05
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
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!
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.