Rounding IF statement?
 

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

Rounding IF statement?
 

try this...

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

Jim Thomlinson


"Matt" wrote:

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

Rounding IF statement?
 

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

Rounding IF statement?
 

=INT(A1+.9)

Regards,
Bernd

Rounding IF statement?
 

Bloody Hell Bernd,

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

Martin

Rounding IF statement?
 

LOL

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

Rounding IF statement?
 

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

"Matt" wrote:

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