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!

 

[brose99]

Yep. That was it. I just figured it out.

When I switched the order of the columns, it threw off the original fx you shared with me. Once I flip flopped the two sections of the fx to compensate for this, the fx you gave me worked perfectly.

Thank you for your help, Dante. Very much appreciated, sir!

 

[DanteAmor]

Again with pleasure. Thanks for the feedback

 

[brose99]

Sorry Dante, I thought I fixed it, and I was wrong. Something is still not right.

I'm trying to post a picture of the worksheet I am working on so you can get a better scope of what I'm dealing with, but it won't let me upload a screenshot from my computer...

 

[brose99]

I'm sorry I am having so much trouble with this.

Let's try this, Dante. Here is the updated problem I am having.



I am trying to write an fx in AL244 so that IF Z244 is >-150, Z244<-109 OR Z244 is >109, Z244<150 AND AB244 is >0.5, AB244<50 OR AB244>=175, then a 1 shows up in AL244. I not, then AL244 remains blank.

I hope I explained that ok.

Thanks in advance.

 

[DanteAmor]

I'm sorry I am having so much trouble with this.

Let's try this, Dante. Here is the updated problem I am having.



I am trying to write an fx in AL244 so that IF Z244 is >-150, Z244<-109 OR Z244 is >109, Z244<150 AND AB244 is >0.5, AB244<50 OR AB244>=175, then a 1 shows up in AL244. I not, then AL244 remains blank.

I hope I explained that ok.

Thanks in advance.

Can you put the formula?
In which cell is the formula that has the problem?

 

[brose99]

Columns
......Z..........AB..........AJ

[TABLE="width: 155"]
<tbody>[TR]
[TD="class: xl67, width: 35, bgcolor: yellow, align: center"]136[/TD]
[TD="class: xl70, width: 35, align: center"]-5[/TD]
[TD="class: xl79, width: 35, bgcolor: red, align: center"]1[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: yellow, align: center"]-146[/TD]
[TD="class: xl71, align: center"]10[/TD]
[TD="class: xl80, align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

Here is the formula I am using in cell AJ236

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

The problem is that the results its producing are not accurate. Cell AB236 has -5 in it. -5 is out of the range I have specified in the above formula, yet in in cell AJ236 a 1 is populated when it shouldn't. I must have screwed something up and was hoping a second pair of eyes might help.

(I have highlighted the squares in red that have a "1" that should not have a "1" per the above formula.)

 

[DanteAmor]

This happens

Z236 = 136


IF(OR

OR this (AND(Z236>-150,Z236<-109), FALSE

OR this AND(Z236>109,Z236<150), TRUE

OR this AND(OR(AND(AB236>0,AB236<50),AB236>=175))) FALSE

The second option is the one that is fulfilled, so come back 1.

----

This is my original formula


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

But if my formula does not work for you, then explain again what the conditions are.


---
In reference to cross posting, only apologize and add the link to this post and in this post add the link to your other page.

 

[brose99]

The formula you just gave me is not picking up any number >175 when I drag it down. Hmmmm....

 

[DanteAmor]

Z236 >= 175
And
AB236 > 109,AB236 < 150

If Z236 = 180
AND
AB236 = 120

Return: 1

It works for me.
I dragdown and works.

If you modify my formula you must share it to revise it again.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...ve-and-negative-number-range.html#post5108729

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.