Nested IF...THEN formula with multiple conditions: Rule of thumb on sequence logic?

[estephenkim]

When creating nested IF...THEN formulas, is there a rule of thumb you use to design the logic sequence of conditions? I'm trying to address five conditions using a nested IF...THEN formula and could use some guidance. Alternatively, if you have suggestions on addressing five conditions using a different approach or formula, then I'm open to them as well.

Thanks,

Stephen

 

[teylyn]

Write it out in words to establish the logic.

"If A1 equals apple, then red. Otherwise, if A1 equals Banana, then yellow ... " Etc. That will help you find the best logical sequence.

Then, check if your version of Excel (which is not listed in your profile) has the IFS() function, which is a lot easier to use than nesting multiple IF() functions.

 

[DanteAmor]

if you have suggestions on addressing five conditions using a different approach or formula

Other alternatives, it may be a helper table, or a search formula, or perhaps some other, but it would be easier to give you a solution if you give examples of what you have and the result you need.

Paste a range of cells here using XL2BB tool.

 

[estephenkim]

Thanks for your responses!

Unfortunately, my MS Excel 2013 doesn't have the IFS() function as it would make things intuitive and easy for me.

I've been able to figure out the formulas for all but one of the cells I need. I need to tweak something in the formula. Perhaps I can get someone to take a look and offer a suggestion?

Project description. I'm modifying a dynamic executive calendar which shows the dates and tasks that need to be completed leading up to and after the executive meeting (and decision) date. The user enters the meeting date into an input cell and the calendar populates with the correct dates and task descriptions.

In this modification, I want to accommodate holidays and meeting duration (whether 1-Day or 2-Day) and have been developing a model task schedule from which the executive calendar (not shown) will source its dates and task descriptions. In the screen capture below, the Task description - regular (Column D) presents the task sequence leading to the executive decision for a standard 1-Day meeting with no holidays. Task description - Holiday adj.; Combined 2 (Column H) presents the task sequence leading to the executive decision for a 2-Day meeting which includes a holiday, Feb 17, 2020, in the schedule. As such, in this scenario, I need the task schedule to return a second executive decision day (which it does in cell H15) and also populate "Task 5" in cell H16 (which it doesn't).

In cell H16, I've got the following formula:

=IF($C16="Holiday","",IFERROR(IF(AND($D$2="2-Day",MATCH("Holiday",$C$14:$C$15,0)),$D14,IF(MATCH("Holiday",$C$14:$C$15,0),$D15)),$D16))

However, the formula isn't returning the value in cell D15 ("Task 5") which is what I want it do so for a 2-Day meeting. I've tried different iterations of the aforementioned formula, but I've not been able to figure out the right configuration. Any help or suggestions would be appreciated...

 

[teylyn]

I've read your post a few times and I still struggle understanding what you want to achieve.

Lose the IFERROR because that masks any errors that could give you useful information.
MATCH("Holiday",$C$14:$C$15,0) will return #N/A, so the whole formula errors out. That's why you don't want IFERROR.
Step through the formula with the Evaluate Formula tool, so you can see what each part of the formula returns.

Then please put the logic into simple words and write it out. Complete this for me:

if C16 equals holiday, return a blank. Otherwise, if D2 says "2-Day" and ..... What is the logic here?

 

[teylyn]

maybe replace the Match with Countif. If it doesn't find "Holiday", the result is simply zero (or FALSE).

=IF($C16="Holiday","",IF(AND($D$2="2-Day",COUNTIF($C$14:$C$15,"Holiday")),$D14,IF(COUNTIF($C$14:$C$15,"Holiday"),$D15,$D16)))

 

[estephenkim]

Got it, teylyn! I've listed below the five conditions that I need to satisfy:

Condition 1. If C16 equals "Holiday," then return a blank (because there will be no meeting or tasks performed on a holiday).

Condition 2. If D2 equals "2-Day" and there is a holiday in cells C14 and C15, then return D14 (because there will be a second executive decision day).

Condition 3. If D2 equals "2-Day" and there's no holiday in cells C14 and C15, then return D15 (because Task 5 will need to be completed the next workday after the 2-Day executive meeting).

Condition 4. If D2 equals "1-Day" and there is a holiday in cells C14 and C15, then return D15 (because Task 5 occurs the next workday after the executive meeting).

Condition 5. If D2 equals "1-Day" and there's no holiday in cells C14 and C15, then return a blank (because Task 5 will have already been completed and there are no more tasks to complete).

 

[estephenkim]

I tried the formula with COUNTIF and it worked fine in the 1-Day scenario, but, in the 2-Day with no holiday, it returned a blank when it should return the value in D15: Task 5. Thanks for the idea, nonetheless!

I'm thinking that I need to nest some additional IF...THEN statements to accommodate all scenarios, but how to logically structure it??? Since there are two divergent paths based on meeting duration (1-Day or 2-Day), I wonder if it's possible to create two streams of IF...THEN statements and integrate them into a single formula...?

 

[Peter_SSs]

Can D2 have any values, including blank, other than "1-day" or "2-day"?

If not, try
=IF(C16="Holiday","",IF(COUNTIF(C14:C15,"Holiday"),INDEX(D14:D15,3-LEFT(D2,1)),IF(D2="2-day",D15,"")))

If D2 can be something else then try
=IF(C16="Holiday","",IF(COUNTIF(C14:C15,"Holiday"),INDEX(D14:D15,3-LEFT(D2,1)),IF(D2="2-day",D15,IF(D2="1-day","","??"))))

 

[estephenkim]

Thank you, Peter_SSs! It worked on both the 1-Day and 2-Day scenarios. Yes, the only options in D2 are blank, 1-Day, and 2-Day.

I like to apply formulas using consistent design wherever possible. Could you help me confirm my understanding of the logic behind this formula so I can decide if I want to apply it to my other cells?

I think it reads if there are any holidays in cells C14 and C15, then retrieve the value from cells D14 and D15 corresponding to the first character in cell D2 (either 1 or 2). If there are no holidays and if D2 equals 2-Day, then return the value in D15 ("Task 5"), else blank.

Do I have this right? Also, I'm not familiar with the 3-LEFT function. Is this essentially the same as LEFT?