Problems with AND for decimals below 1

[camilof]

Hi there,

Could anyone tell me why I get FALSE when B4 is a decimal between 0 and 1 (say .2) or between 0 and -1 in this formula? It obviously has something to do with my AND functions, but not sure how to fix it.

=IF(AND(B4<0,B4>=-(0.9)),B4=-1,IF(AND(B4>=0,B4<1),B4=1,ROUND(B4,0)))

Thanks!

[oldchippy]

Does this work for you?

=IF(AND(B4<0,B4>=-0.9),-1,IF(AND(B4>=0,B4<1,),1,ROUND(B4,0)))

[daddylonglegs]

Your syntax is wrong, you're returning a test like b4=1, which will give either TRUE or FALSE, I assume you want to round B4 to the nearest integer but avoid zero, if so try

=IF(ROUND(B4,0),ROUND(B4,0),ROUNDUP(B4,0))

but what result do you want if B4=0?

[camilof]

oldchippy,

you're right--having "B4=-1" and "B4=1" in my formula was just stupidity--and bad coffee.

daddylonglegs, what I'm actually trying to do is:

a) round up any decimal between 0 and 1 to 1, eg .3 rounds to 1
b) round 'down' any decimal between 0 and -1 to -1, eg -.7 rounds to -1
c) round 0 up to 1
d) round all other numbers to the nearest integer, eg 2.3 to 2 or -2.8 to -3

Thanks all.

[camilof]

by the way, daddylonglegs, your formula works like a charm -except- for the 0 part, that is, it should return '1' when 'B4=0'.

the pathetic part is that I can't quite figure out how your formula works... I'm going to keep studying it.

[daddylonglegs]

I believe my suggestion above will do what you want except for when B4=0, try

=IF(B4="","",IF(OR(B4<-1,B4>1),ROUND(B4,0),IF(B4<0,-1,1)))

[camilof]

That last formula worked great, AND I understand it. However, what is the purpose of the initial section =IF(B4="","" ? It just checks whether there is a value at all in B4, right?

As for the previous idea you gave me, what does =IF(ROUND(B4,0) check for?

Thanks!

[oldchippy]

Hi,

If B4 is blank then without =IF(B4="","" B4 would return 1

ROUND(B4,0) rounds to the nearest Integer

[daddylonglegs]

If you use

=IF(n, do if true, do if false)

where n is a number then zero is evaluated as false, all other numbers as true so when you use

=IF(ROUND(B4,0),ROUND(B4,0),ROUNDUP(B4,0))

Then when -0.5 < B4 < 0.5 ROUND(B4,0) will evaluate to zero so ROUNDUP will apply, ROUNDUP rounds away from zero so all values except zero will become 1 or -1. The formula is more transparent as

=IF(ROUND(B4,0)<>0,ROUND(B4,0),ROUNDUP(B4,0))