How Do I Nest Complex If, AND, OR Statements Together

JohnnyAngel · Apr 18, 2011

All,

I'm trying to create this complex IF statement that I think will contain IF, AND, OR Statements. How do I properly piece these together as one formula to utilize?

Basically, the criteria is as follows:

IF the value in E2 is "RETL" AND the sum of cells P2:Q2 is >$2000, AND the date in cell T2 is less than 3/1/11 if true code with "C" if false code with "A".

IF the value in E2 is "WKLY" AND the sum of cells P2:Q2 is >$2000, AND T2 is less than 3/1/11, if true code with "E" if false code with "D".

IF the value in E2 is "DLY" AND the sum of cells N2:Q2 is >1, AND T2 is less than 3/15/11 if true code with "E" if false code with "B".

<DATE(2011,3,1)),"C","A")

Here's an example of where I am at so far:

 =IF(AND(E2="RETL", SUM(P2:Q2)>2000, T2<date(2011,3,1)),"c","a")> </date(2011,3,1)),"c","a")>[FONT=&quot]<date(2011,3,1)),"c","a")></date(2011,3,1)),"c","a")>[/FONT]<date(2011,3,1)),"c","a")>
[FONT=&quot]<date(2011,3,1)),"c","a")></date(2011,3,1)),"c","a")>[/FONT]
<date(2011,3,1)),"c","a")>
OR

=IF(AND(E2="WKLY", SUM(N2:Q2)>1000,T2 </date(2011,3,1)),"c","a")>[FONT=&quot]<date(2011,3,1)),"e","d")></date(2011,3,1)),"e","d")>[/FONT]
[FONT=&quot]<date(2011,3,1)),"e","d")></date(2011,3,1)),"e","d")>[/FONT]
<date(2011,3,1)),"c","a")><date(2011,3,1)),"e","d")>
OR

=IF(AND(E2="DLY", SUM(N2:Q2)>1,T2 </date(2011,3,1)),"e","d")></date(2011,3,1)),"c","a")>[FONT=&quot]<date(2011,3,1)),"e","d")></date(2011,3,1)),"e","d")>[/FONT]
[FONT=&quot]<date(2011,3,1)),"e","d")></date(2011,3,1)),"e","d")>[/FONT]
<date(2011,3,1)),"c","a")><date(2011,3,1)),"e","d")><date(2011,3,1)),"e","b")>

You're expertise would be greatly appreciated!

</date(2011,3,1)),"e","b")></date(2011,3,1)),"e","d")></date(2011,3,1)),"c","a")></date(2011,3,1)),"c","a")>

gsistek · Apr 18, 2011

Its a bit lengthy:

=IF(AND(SUM(P2:Q2)>2000,T2<40603),IF(E2="RETL","C","E"),IF(E2="RETL","A",IF(E2="WKLY","D","B")))

JohnnyAngel · Apr 18, 2011

By the way, underneath is the formula I have created with the DATE criteria. I forgot to include that in the original post.

=IF(AND(E2="RETL", SUM(P2:Q2)>2000, T2<DATE(2011,3,1)),"C","A")

OR

=IF(AND(E2="WKLY",SUM(N2:Q2)>1000,
T2<DATE(2011,3,1)),"E","D")

OR
=IF(AND(E2="DLY",SUM(N2:Q2)>1000,
T2<DATE(2011,3,1)),"E","D")

JohnnyAngel · Apr 18, 2011

Thank you so much! Can I replace the T2<40603 with a DATE(2011,3,1) formula?

Also, the RETL has a criteria of $2,000, WKLY $1,000, and DLY $1.00. How would that look? Or does your formula cover that criteria some how?

JohnnyAngel · Apr 18, 2011

I want the formula to sort of look like this so it captures all the different types of criteria to meet and code. Is this possible?

=IF(AND(T2<date(2011,3,1)),if(e2="retl"><DATE(2011,3,1)) IF(E2="RETL">2000,"E","B"),IF(E2="WKLY">1000,"E","B",IF(E2="DLY">1,"E","B"))</date(2011,3,1)),if(e2="retl">

JohnnyAngel · Apr 18, 2011

I'm trying to post this one particular criteria and its being converted to something weird. I want the formula to say cell T2 is less than 3/1/11 in the criteria and for each IF statement to have its own criteria to search for and code. RETL >2000 , WKLY >1000, DLY >0. Then, I want it to return a code for each argument for when its true or false.

Hopefully the below date criteria shows up right on this post.

T2 <DATE (2011 , 3 , 1)

jasonb75 · Apr 27, 2011

Based on the criteria in the original question

Code:

=CHOOSE(MATCH(E2,{"RETL","WKLY","DLY"},0),
  IF(AND(SUM(P2:Q2)>2000,T2<"03/01/2011"),"C","A"),
  IF(AND(SUM(P2:Q2)>1000,T2<"03/01/2011"),"E","D"),
  IF(AND(SUM(P2:Q2)>1,T2<"03/15/2011"),"E","B"))

JohnnyAngel · Apr 27, 2011

It worked exceptionally. Thank you very much Jason, I really appreciate it.

Just so that I can better understand the functions used, the CHOOSE & MATCH functions coupled, will return results based on the order that the Retl, Wkly, DLY was set up as in accordance to the order the dollar amounts, dates, and code criteria specified correct?

In other words, if I switch the order of Retl, Wkly, DLY, I must change the order of the rest of the criteria to return the criteria established for Retl, Wkly, Dly. Otherwise, the results will not match up to my criteria of dollar amounts, dates and codes as desired.

jasonb75 · Apr 27, 2011

That's pretty much it,

MATCH will return a value of 1,2, or 3 (the position of whatever is in E2 within the array constant). (array constant is the bit enclosed in {})

Based on the result, CHOOSE then does what the name suggests and chooses the relevant formula.

So, yes, if you change the order of retl, wkly, dly, then you would need to change the order of the other criteria to match. If you look at the formula, I did post in that format deliberately, 1 criteria per line, so that it's easier to understand.

Maybe not the most efficient way to achieve the result desired, but I would say the easiest to understand if you need to amend it.

barry houdini · Apr 27, 2011

...you need a small tweak to Jason's date comparisons, though. If you use this

T2< "03/01/2011"

Then that will always return TRUE......for any date in T2 because "03/01/2011" is interpreted as a text value, not a date. You need a "co-ercer" like this

T2< "03/01/2011"+0

or better to use DATE function as per your original suggestion, which is also unambiguous, i.e.

T2< DATE(2011,3,1)

How Do I Nest Complex If, AND, OR Statements Together

JohnnyAngel

Board Regular

Excel Facts

gsistek

Well-known Member

JohnnyAngel

Board Regular

JohnnyAngel

Board Regular

JohnnyAngel

Board Regular

JohnnyAngel

Board Regular

jasonb75

Well-known Member

JohnnyAngel

Board Regular

jasonb75

Well-known Member

barry houdini

MrExcel MVP

Similar threads

Forum statistics

Share this page

How Do I Nest Complex If, AND, OR Statements Together

Board Regular

Excel Facts

Well-known Member

Board Regular

Board Regular

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock