[Solved] Rounding up to the nearest .05 or .09

[slyOO]

I have created a formula that mostly works, but would like to know if there is a better way to achieve rounding up to the nearest .05 or .09.

Here is the formula I came up with:

Code: Select all

=IF(ROUND(MOD(A1;0.1);2)<0.05;CEILING(A1;0.05);CEILING(A1;0.1)-0.01)

There are two flaws with this formula I haven't worked out. The first occurs when the value of A1 has a zero in the 1/100th position (e.g. if A1=6.60, it will not round with my formula). The second occurs when there is a 5 in the 1/100th position (e.g. if A1=6.65, the formula will resolve to 6.69). I would prefer to leave the value unchanged in this case.

I appreciate any and all help!

[RusselB]

Welcome to the Forums.
While your topic seemed familiar, I was unable to find any other topic on the forum.
That said, the easiest way to handle your problem situations is to simply modify your current formula.
I'd like to suggest the following:

Code: Select all

=IF(VALUE(RIGHT(A1*100;1))/5;A1;IF(ROUND(MOD(A1;0.1);2)<0.05;CEILING(A1;0.05);CEILING(A1;0.1)-0.01))

Long day so that's the best I can do now. I'll see if I can optimize it more in the morning.

[lader]

simpler variant

Code: Select all

=IF(MOD(INT(A1*100);10)>5; 0.09; 0.05)+INT(A1*10)/10

[slyOO]

Thank you for the replies.

simpler variant

Code: Select all

=IF(MOD(INT(A1*100);10)>5; 0.09; 0.05)+INT(A1*10)/10

This worked perfectly and remedied the two issues I was having with my formula. Thank you!