Nested IF AND OR Statement

bluegold

Active Member
Joined
Jun 21, 2009
Messages
279
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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))

 
Upvote 0
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
JK
1FALSEFALSE
Sheet264
Cell Formulas
RangeFormula
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)))
 
Upvote 0
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
 
Upvote 0
Ok,

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


Book1
JK
1FALSEFALSE
Sheet264
Cell Formulas
RangeFormula
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))))
 
Upvote 0
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"}
 
Last edited:
Upvote 0
Try this:


Book1
K
1FALSE
Sheet264
Cell Formulas
RangeFormula
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))))
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top