Rounding IF statement?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Bloody Hell Bernd,

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

Martin
 
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
 
Back
Top