Rounding IF statement?

[Matt]

Hello,
I'd like to setup a roundup or rounddown if statement stating:
If the value of A1 is x.1 to round up to the next whole number; ie (if A1 =
2.1 to round up to 3) but if A1 is x.0x to round down to the next whole
number; ie ( if A1 = 2.09 to round down to 2)

Is this at all possible?
TIA

 

[Jim Thomlinson]

try this...

=IF(MOD(A4,1)<0.1,ROUNDDOWN(A4,0),ROUNDUP(A4,0))

 

[MartinW]

Hi Matt,

This will work but there's probably a daintier way to do it.

=IF(A1-INT(A1)<0.1,ROUNDDOWN(A1,0),ROUNDUP(A1,0))

HTH
Martin

 

[Bernd]

=INT(A1+.9)

Regards,
Bernd

 

[MartinW]

Bloody Hell Bernd,

I know I said "daintier", but that is just downright immoral!!

Martin

 

[Matt]

LOL

thanks everyone! They all seem to be doing the trick.

 

[Jerry W. Lewis]

How precisely do you need to recognize the boundary condition? Excel works
in binary, and the decimal fraction .1 has no exact representation in binary,
so writing formulas that will always to the right thing at the boundary is
not trivial.

Jim and Martin's solutions round down in many case where they should round
up, such as 4.1.

Bernd's solution rounds up in many cases where it should round down, such as
0.0999999999999999.

I think that
=IF(A1-(INT(A1)+0.1)<0,ROUNDDOWN(A1,0),ROUNDUP(A1,0))
will always correctly recognize the boundary.

Jerry