Nested IF AND OR Statement

[bluegold]

Hi guys,

Bugger if I can make this work with the correct syntax. So I want a formula that returns TRUE if the 1st section is met AND either of the 2nd sections. Does this make sense?


1st section
IF(AND($D2>2.29,$D2<7.51,$AP2<"",$A2<=TODAY()))

Then

2nd section
IF(AND($AR2="afl", $J2>1,$I2<-1)) + must meet 1st section

OR

IF(AND($AR2="nrl OR sl", $AI2>1.67, $O2<2.35)) + must meet 1st section

 

[bluegold]

Anyone?

 

[etaf]

1st condition
AND($D2>2.29,$D2<7.51,$AP2<"",$A2<=TODAY(), $AR2="afl", $J2>1,$I2<-1)


2nd condition
AND($D2>2.29,$D2<7.51,$AP2<"",$A2<=TODAY(), OR($AR2="nrl", $AR2= "sl"), $AI2>1.67, $O2<2.35)

now test for either of these

OR (AND($D2>2.29,$D2<7.51,$AP2<"",$A2<=TODAY(), $AR2="afl", $J2>1,$I2<-1) , AND($D2>2.29,$D2<7.51,$AP2<"",$A2<=TODAY(), OR($AR2="nrl", $AR2= "sl"), $AI2>1.67, $O2<2.35))

 

[jtakw]

Hi,

A few things:

In the 1st section, you have $AP2<"" ($AP2 less than Blank), I've assumed you meant $AP2<>"" ($AP2 is Not Blank)
In the Last section, you have $AR2="nrl OR sl", Not sure if you mean $AR2 equal the literal Text string "nrl OR sl", or if you mean $AR2= either "nrl" or "sl", I'm giving you Both.
Since you want a TRUE/FALSE result, we don't need any IF statements:

Book1
	J	K
1	FALSE	FALSE
Sheet264

Cell Formulas
Range	Formula
J1	=AND($D2>2.29,$D2<7.51,$AP2<>"",$A2<=TODAY(),OR(AND($AR2="afl", $J2>1,$I2<-1),AND($AR2="nrl OR sl", $AI2>1.67, $O2<2.35)))
K1	=AND($D2>2.29,$D2<7.51,$AP2<>"",$A2<=TODAY(),OR(AND($AR2="afl", $J2>1,$I2<-1),AND(OR($AR2={"nrl","sl"}), $AI2>1.67, $O2<2.35)))

 

[bluegold]

OK sorry guys I realized I have worded my question wrong, see below. You will see the corrections in bold red.
So the 1st section must be met always, then there are basically 4 conditions that can each be met.

1st section
IF(AND($D2>2.29,$D2<7.51,$AP2<>"",$A2<=TODAY()))

Then

2nd section
IF(AND($AR2="afl", $J2>1 OR $I2<-1)) + must meet 1st section

OR

IF(AND($AR2="nrl OR sl", $AI2>1.67 OR $O2=1)) + must meet 1st section

 

[jtakw]

Ok,

Still, the last section, for $AR2="nrl OR sl" , I'm giving you both:

Book1
	J	K
1	FALSE	FALSE
Sheet264

Cell Formulas
Range	Formula
J1	=AND($D2>2.29,$D2<7.51,$AP2<>"",$A2<=TODAY(),OR(AND($AR2="afl",OR($J2>1,$I2<-1)),AND($AR2="nrl OR sl",OR($AI2>1.67,$O2=1))))
K1	=AND($D2>2.29,$D2<7.51,$AP2<>"",$A2<=TODAY(),OR(AND($AR2="afl",OR($J2>1,$I2<-1)),AND(OR($AR2={"nrl","sl"}),OR($AI2>1.67,$O2=1))))

 

[bluegold]

Works great jtakw. How hard would it be to add to formula as follows; Its just that I have a bunch of cells that return the string "nd" and this formula picks these up when it shouldn't. Changes in red.


=AND($D2>2.29,$D2<7.51,$AP2<>"",$A2<=TODAY(),OR(AND($AR2="afl",OR($J2>1AND $J2<>"nd",$I2<-1 AND $I2<>"nd")),AND(OR($AR2={"nrl","sl"}),OR($AI2>1.67 AND $AI2<>"nd",$O2=1 AND $O2<>"nd"))))

Oh BTW I meant {"nrl","sl"}

 

[jtakw]

Try this:

Book1
	K
1	FALSE
Sheet264

Cell Formulas
Range	Formula
K1	=AND($D2>2.29,$D2<7.51,$AP2<>"",$A2<=TODAY(),OR(AND($AR2="afl",OR(N($J2)>1,N($I2)<-1)),AND(OR($AR2={"nrl","sl"}),OR(N($AI2)>1.67,N($O2)=1))))

 

[bluegold]

Fully tested and works perfectly, thankyou very much jtakw!

 

[jtakw]

You're welcome.