• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested If statement using multiple conditions

D

Dinesh_Excel

Member
Hi All,

I need help for the below output after following some conditions .

Please find attached for your reference.

Output
4
3
2
1
5
6


Comments
If the Condition 1 is "closed" then output should be "4".
If the condition 1 is "OPEN" AND condition 2 equals "0" then the ouput should be "3"
If the Condition 1 is "OPEN" AND Condition 2 is "Suspended" and Condition 3 is Greater than "0" then the ouput should be "3"
If the Condition 1 is "OPEN" AND Condition 2 is "Suspended" and Condition 4 is "awaiting for approval" then the ouput should be "4"
If the Condition 1 is "OPEN" AND Condition 2 is "Suspended" and Condition 3 is "n/a" then the ouput should be "5"
If the Condition 1 is "OPEN" AND Condition 2 is "Suspended" and Condition 3 is "to be quoted" then the ouput should be "5"
 

Attachments

  • comments.xlsx
    9.2 KB · Views: 8
To put this more simple..

If condition 1= “closed” then output is 4.

If condition 1= “open” and condition2 =is Zero (0) then output is 5.

If condition 1= “open” and condition2 =is “Suspended” and condition 4 is greater than 0(Zero) then output is 6.

If condition 1= open and condition2 =is “Suspended” and condition 4 “awaiting for Approval” then output is 7.

If condition 1= “open “and condition2 =is “Suspended” and condition 3 “n/a” then output is 8.

If condition 1= “open” and condition2 =is “Suspended” and condition 3 “to be quoted” then output is 9.
 
What should happen in the event of other combinaitons? e.g. If the Condition 1 is "OPEN" AND Condition 2 is "Suspended" and Condition 3 is "0"?

By far the easiest way to do this is via a lookup table, where you have a lookup table that concatenates (joins) Condition 1 & 2 & 3 & 4 together into a key, and then you list the value in the next column for each specific combination. THen you do a VLOOKUP along the following lines:
=VLOOKUP(C9&D9&E9&F9,SomeLookupTable,2,0)

You can also use wildcards in that VLOOKUP to catch other combinaitons that might not be captured by my 'starter for ten' but I'd need to know more about what other values users might put in these fields to set it up.
 
Hi Jeff,


Assuming that the "Output" is in column A and the rest of the data is in columns B, C, D and E.

Putting this formula in A2, but the issue is instead the output as 7 for one conditions its throwing as "6".

Can you help now.

Below is the formula that I have arrived at with some flaws.

=IF(B2="CLOSED","4",IF(AND(B2="OPEN",C2=0),"5",IF(AND(B2="OPEN",C2="SUSPENDED",E2>0),"6",IF(AND(B2="OPEN",C2="SUSPENDED",E2="AWAITING FOR APPROVAL"),"7",IF(AND(B2="OPEN",C2="SUSPENDED",D2="N/A"),"8",IF(AND(B2="OPEN",C2="SUSPENDED",D2="to be quoted"),"9",""))))))
 
Dinesh: again as per my earlier post, by far the easiest and most efficient way to do this is via a lookup table. Using a Nested IF statements is simply too complicated. I can help you with a lookup table to fix this once you've answered my question above, but I can't help you with nested IF statements because I don't think they are a good solution.
 
Hi Dinesh,

Your comments and are output contents are not matching each other.
However, I have tried something to get closer to your requirement, here is what I understood:

=IF(AND(C9="Open",D9="Suspended",E9="To be quoted"),6,IF(AND(C9="Open",D9="Suspended",E9="N/A"),5,IF(AND(C9="Open",D9="Suspended",F9="Awaiting for approval"),4,IF(AND(C9="Open",D9="Suspended",F9>0),2,IF(C9="Closed",4,IF(AND(C9="Open",D9=0),3,""))))))

Red highlighted:
1) Remove double quote marks for awaiting for approval in your sheet
2) insert any valid number greater than Zero instead of ">0" in your sheet. In case you want to use hard coded condition, replace F9>0 with F9=">0"

You can see this is a long formula, doing multiple calculations and also may not feasible for some other conditions which you haven't mentioned.

Agree with Sir Jeff, nested ifs will be complicated to add / edit more conditions, try setting up a lookup table with all conditions.

Regards,
 
Convinced, Jeff and Khalid....these if statements are way too long :(

Jeff-What should happen in the event of other combinaitons? e.g. If the Condition 1 is "OPEN" AND Condition 2 is "Suspended" and Condition 3 is "0"?

Dinesh: For the above scenario, we need to move to next condition, there is no such combination required.

To make this simple, have attahced "key" with clear explanation for your reference.

Thanks Again Jeff and Khalid for your time.
 

Attachments

  • Key.xlsx
    8.7 KB · Views: 5
Dinesh: Just to be clear, will there ever be something in both the 'Quote Status' AND the 'No of Items' cells for a particular line at the same time?

e.g. Opened, Suspended, DIR, 5 or any of the other combinations I've added in the below image?Clarification.gif
 
No Jeff, all the first 6 conditions are static and there is no change in the value ...the execution should be like, once the first condition gets done for eg: "Closed",then the next condition should come in to picture.

All the above should be as the same order which was given in my attahced sheet.

Just play around with the conditions given by me in the attached from my previous threads.

Cheers
 
Great. One more question: is there an upper limit on that number? i.e. will there say always be between 0 and say 10 in that field? Or could it be any number?
 
You can actually use a much simpler IF statement than those above to achieve this.
I can't upload a file, but if you change your data into an Excel Table (using the Ctrl T shortcut) then this will do it:
=IF([@Status]="Closed",2,IF([@Delivery]=0,3,IF([@[Quote status ]]="DIR",6,IF([@[Quote status ]]="TO BE QUOTED",7,IF([@[No of Items]]="Awaiting for Approval",5,4)))))
 
Whoops, left a condition out. Here's the corrected one.
=IF([@Status]="Closed",2,IF([@Delivery]=0,3,IF([@[Quote status ]]="DIR",6,IF([@[Quote status ]]="TO BE QUOTED",7,IF([@[No of Items]]="Awaiting for Approval",5,IF([@[No of Items]]>0,4,NA()))))))
 
Jeff , the ones which are blank in the status column the output is automatically picking up as "3"

Attached file for your reference.

PS : Have added few more conditions ...not sure if these can be done with the same strategy.
 

Attachments

  • Key.xlsx
    13.6 KB · Views: 7
Dinesh: that's a pretty massive increase in requirements. That kind of logic can only be handled well by using a Select Case statement within a User Defined Funciton (UDF).

In other words, you need a macro. Furthermore, the coding of the macro will be greatly simplified if you can change the data into an Excel Table.

Will it be okay to use a macro and change this data into an Excel Table?
 
You must log in or register to reply here.
Back
Top