Rounding up based on a decimal value

[Kelley]

I would like to be able to round a number based on a decimal value. For
example, if the result is 23.7, 23.8, or 23.9, I want the number to round up
to 24. I always want to round up for .7 and higher.
Is there a way to code this?

 

[SteveW]

add 0.3, and rounddown
23.6 would be 23.9 -> 23
23.7 would be 24.0 -> 24
23.8 would be 24.1 -> 24

so if values in a2,a3 etc b2=rounddown(a2+0.3)

Steve

 

[Kelley]

Thanks. If the result is .0, .1, .2, .3, .4, .5, or .6, I need the decimal
to stay. If the result is .7, I need it to round up to the nearest integer.
It the result is .8, or .9, I need it to round to .1 or .2.

 

[SteveW]

That's not what you said the first time

a2 has value
b2 = if((int(a2)-a2)<0.7,a2,a2+0.3)

Steve

 

[Jerry W. Lewis]

I think you meant =IF((A2-INT(A2))<0.7,A2,A2+0.3) which presumes that A2>0.

Also, You need to be a bit more careful about the impact of finite precision
binary representations. For instance, if A2 contains 8.7, then
((A2-INT(A2))<0.7 is TRUE.

Jerry

 

[Kelley]

Thanks to both of you for your help. Using Steve's response, I was able to
figure out Jerry's solution yesterday. I also changed the 0.7 to <0.6666 and
it works.
Thanks again.

 

[SteveW]

Ta for the feedback.

Excel is pretty accurate, but all floating point stuff needs care.

If 0.6666 covers the data that you are working with thats the best way.

Steve