IF(AND(OR multiple step fx including positive and negative integers

[brose99]

[TABLE="class: grid, width: 300, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]w[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]z[/TD]
[TD="align: center"]aa[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: center"]251[/TD]
[TD="align: center"]-108[/TD]
[TD="align: center"]159[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: center"]-231[/TD]
[TD="align: center"]118[/TD]
[TD="align: center"]-149[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What is the correct way to enter the following multi step fx to satisfy the following:

In cell Z8 = IF W8 is > 0 AND X8 < 0, OR IF W8 < 0 AND X8 > 0, AND X8 <=-150, THEN 1, ""
In cell AA8 = IF W8 is > 0 AND X8 <0, OR IF W8 <0 AND X8 > 0, AND Y8 > 39.5 AND Y8 < 190, THEN 1, ""

Thanks in advance for your help.

 

[kweaver]

Is this the right logic? Would be a help if there were more values in W, X, Y to check against the formula with the results you expect.

Z column:

=IF(AND(X8<=-150,OR(AND(W8>0,X8<0),AND(W8<0,X8>0))),1,"")

AA:

=IF(AND(OR(AND(W8>0,X8<0),AND(W8<0,X8>0)),AND(Y8>39.5,Y8<190)),1,"")

 

[Aryatect]

Hi,

See if this works for you for what I could understand from your requirement, if this is not correct please do give some more example of the data set above and expected output example

Book1
	W	X	Y	Z	AA
8	251	-108	159		1
9	-231	118	-149
Sheet2

Cell Formulas
Range	Formula
Z8	=IF(AND(OR(AND(W8>0,X8<0),AND(W8<0,X8>0)),X8<=-150),1,"")
AA8	=IF(AND(OR(AND(W8>0,X8<0),AND(W8<0,X8>0)),Y8>39.5,Y8<190),1,"")

 

[brose99]

You are correct, but it looks like I am trying to cram too many arguments into one fx.

Please allow me to simplify my question:

Can you help me write the first part of this fx, please?

In cell Z8 = IF W8 is > 0 AND X8 < 0, OR IF W8 < 0 AND X8 > 0, THEN 1, ""

Nevermind....I just answered this question with trial and error...

 

[brose99]

Hi,

See if this works for you for what I could understand from your requirement, if this is not correct please do give some more example of the data set above and expected output example

	W	X	Y	Z	AA

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]8[/TD]
[TD="align: right"]251[/TD]
[TD="align: right"]-108[/TD]
[TD="align: right"]159[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]-231[/TD]
[TD="align: right"]118[/TD]
[TD="align: right"]-149[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Z8[/TH]
[TD="align: left"]=IF(AND(OR(AND(W8>0,X8<0),AND(W8<0,X8>0)),X8<=-150),1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AA8[/TH]
[TD="align: left"]=IF(AND(OR(AND(W8>0,X8<0),AND(W8<0,X8>0)),Y8>39.5,Y8<190),1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

BOOM! Nailed it Aryatect! THANK YOU!

 

[Aryatect]

Glad could help