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

[brose99]

	w	x	y	z	aa
8	251	-108	159
9	-231	118	-149
10

<tbody>
</tbody>

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
8	251	-108	159		1
9	-231	118	-149

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell 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,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

BOOM! Nailed it Aryatect! THANK YOU!

 

[Aryatect]

Glad could help