IF(AND(OR multi step fx help needed

[brose99]

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]-145[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]-71[/TD]
[TD="align: center"]140[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]130[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]-22[/TD]
[TD="align: center"]-127[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

How do you write a proper IF(AND(OR fx in cell C1 that I can drag down thru C4 that states:
if the number in cell A1 is >0 and <50 AND
the number in cell B1 is >-150, <-109 *OR* >109, <150,
then cells C1 populates a 1 if it does, and a "" if it doesn't?

I hope I explained that ok.

The final results should have a 1 in C1 and a 1 in C3

Thanks in advance for any help!

 

[sadboy309]

Bạn thử công thức:
=IF(A1="","", ((A1> 0) * (A1 <50)) * ((B1> -150) * (B1 <-109) + (B1> 109) * (B1 <150)))

 

[DanteAmor]

How about:



=IF(AND(A1>0,A1<50,OR(AND(B1>-150,B1<-109),AND(B1>109,B1<150))),1,"")

 

[brose99]

How about:



=IF(AND(A1>0,A1<50,OR(AND(B1>-150,B1<-109),AND(B1>109,B1<150))),1,"")

NAILED IT! Gracias, DanteAmor! Much appreciated, amigo!

 

[DanteAmor]

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

 

[brose99]

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

Quick updated question, Dante...

I need to update the first portion of the above IF(OR(AND fx so that it also includes all numbers greater than or equal to 175. For example: A1>0, A1<50 OR A1>=175

The 2nd portion of the formula *...OR(AND(B1>-150,B1<-109),AND(B1>109,B1<150))),1,"") * will remain the same.

I've tried tinkering with the first part of this fx to make this adjustment and I keep messing it up. I know it's a simple fix for someone much better than me. Can you, or anyone else, assist me with this, please?

Thanks!

 

[DanteAmor]

Try:

=IF(AND(OR(AND(A1>0,A1<50),A1>=175),OR(AND(B1>-150,B1<-109),AND(B1>109,B1<150))),1,"")

 

[brose99]

Try:

=IF(AND(OR(AND(A1>0,A1<50),A1>=175),OR(AND(B1>-150,B1<-109),AND(B1>109,B1<150))),1,"")

The formula looks right, and it doesn't give me an error message when I type it in, but it's still not picking up any number >=175 in A1. Hmmmm.....

 

[DanteAmor]

>=175 in A1, but in B1 you must have > =- 150 and < 109 ...

 

[brose99]

Still having the same issues. I double checked my work and its typed in correctly.

Does it matter which IF(AND(OR fx comes first? Because due to formatting issues, I had to flip/flop columns A&B from my original message. The new data set looks like this:
[TABLE="class: cms_table, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]-145[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]140[/TD]
[TD="align: center"]71[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]130[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]-127[/TD]
[TD="align: center"]-22



[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]