IF, Or, And statements

[ItalianPlatinum]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Class
[/TD]
[TD]Amount
[/TD]
[TD]Yes (Formula Below)
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]-100
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]200
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hello,
I am having considerable trouble trying to get a combo of all three to function as intended. A very simplistic explanation. If 1 class is greater than 0 and 1 class is less than 0 then take the class with the greater ABS.

My Formula:=IF(OR(AND(B2<0,B3>0,B2>0,B3<0),AND(ABS(B2)<ABS(B3))),"Yes","No")

Formula works, but breaks down if cell B2 is positive 100 it also gives me a value of yes. Anyone able to help

 

[DanteAmor]

But a value can not be greater than 0 and also less than 0.

f 1 class is greater than 0 and 1 class is less than 0 then

Better explain with your examples what data you have and what result you expect in each case. List all possible cases.

 

[ItalianPlatinum]

But a value can not be greater than 0 and also less than 0.



Better explain with your examples what data you have and what result you expect in each case. List all possible cases.

So maybe that is the problem. So I have 4 options/scenarios. Options 3 and 4 breakdown in my formula

Option 1: neither class has values (if true then Yes)
Option 2: Both Class have positive or both have negative values (if true then Yes)
Option 3: One of the classes is positive and the other is negative, ABS of Class 1 > 2 (if true then Yes)
Option 4: One of the classes is positive and the other is negative, ABS of Class 1 < 2 (if true then Yes)

 

[DanteAmor]

Try this

=IF(AND(B2="",B3=""),"Yes both empty",
IF(OR(AND(B2>0,B3>0),AND(B2<0,B3<0)),"Yes Both positive or negative",
IF(AND(B2<0,B3>0),IF(ABS(B2)>B3,"Yes class1 greater",
IF(AND(B2>0,B3<0),IF(B2<ABS(B3),"Yes class1 lesser","No Class1 greater"),"Undefined A")),"Undefinido B")))

I put different "Yes" and its description so you can do your tests and see the results.
You also have several cases that are not met, I also put a "No" and "Undefined."


Undefined examples:
-100 and 200
200 and -100
0 and 0
0 and 100
0 and -100
empty and 100
100 and empty
100 and -100
-200 and 200
100 and 0
-100 and 0

 

[ItalianPlatinum]

Try this

=IF(AND(B2="",B3=""),"Yes both empty",
IF(OR(AND(B2>0,B3>0),AND(B2<0,B3<0)),"Yes Both positive or negative",
IF(AND(B2<0,B3>0),IF(ABS(B2)>B3,"Yes class1 greater",
IF(AND(B2>0,B3<0),IF(B2<abs(b3),"yes class1="" lesser"<="" font="">,"No Class1 greater"),"Undefined A")),"Undefinido B")))

I put different "Yes" and its description so you can do your tests and see the results.
You also have several cases that are not met, I also put a "No" and "Undefined."


Undefined examples:
-100 and 200
200 and -100
0 and 0
0 and 100
0 and -100
empty and 100
100 and empty
100 and -100
-200 and 200
100 and 0
-100 and 0

The 4th formula I can't seem to replicate. is it missing an expression? Or maybe I am just copying it over wrong</abs(b3),"yes>

 

[ItalianPlatinum]

The 4th formula I can't seem to replicate. is it missing an expression? Or maybe I am just copying it over wrong[/COLOR]

So I created a 3a, 3b, 4a, 4b. I guess how could I combine the formulas?

3a: =IF(AND(B2<0,B3>0,ABS(B2)>ABS(B3)),"Yes","No")
3b: =IF(AND(B2>0,B3<0,ABS(B2)>ABS(B3)),"Yes","No")
4a: <abs(b3)),"yes","no")
=IF(AND(B2<0,B3>0,ABS(B2)<ABS(B3)),"Yes","No")
4b: =IF(AND(B2>0,B3<0,ABS(B2)<ABS(B3)),"Yes","No")

<abs(b3)),"yes","no")< html=""></abs(b3)),"yes","no")<></abs(b3)),"yes","no")

 

[ItalianPlatinum]

Not sure why 4a and 4b are getting cut off.

4a: IF(AND(B2<0,B3>0,ABS(B2)<ABS(B3)),"Yes","No")
4b: IF(AND(B2>0,B3<0,ABS(B2)<ABS(B3)),"Yes","No")

 

[ItalianPlatinum]

Not sure why 4a and 4b are getting cut off.

4a: IF(AND(B2<0,B3>0,ABS(B2)<abs(b3)),"yes","no")
4b: IF(AND(B2>0,B3<0,ABS(B2)<abs(b3)),"yes","no")[ quote]


I AM GOOD. I solved it! Thanks for leading me to the light Dante</abs(b3)),"yes","no")[></abs(b3)),"yes","no")

 

[DanteAmor]

It is a single formula a big formula:

=IF(AND(B2="",B3=""),"Yes both empty",IF(OR(AND(B2>0,B3>0),AND(B2<0,B3<0)),"Yes Both positive or negative",IF(AND(B2<0,B3>0),IF(ABS(B2)>B3,"Yes class1 greater",IF(AND(B2>0,B3<0),IF(B2<ABS(B3),"Yes class1 lesser","No Class1 greater"),"Undefined A")),"Undefinido B")))

I'm glad to help you. Thanks for the feedback.