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]

This is not a function called 3-LEFT(). The first character on the left in cell D2 (presumably a number) is subtracted from the number 3

3 minus LEFT(D2,1)

3-LEFT(D2,1)

 

[estephenkim]

Thank you, teylyn! I finally figured out what that meant after I pored over it awhile.

One issue with the suggested formula I didn't initially realize is that when D2 is blank, H16 returns #VALUE! It doesn't appear that INDEX with 3-LEFT is accounting for the possibility of a blank D2. I need to figure out how to incorporate this third option. If anyone has any suggestions, I'd welcome them!

 

[estephenkim]

I've enclosed below the possible output options for cell H16 given the meeting duration (2-Day, 1-Day, blank) and holiday (Yes, No) input options.

Building off of what Peter_SSs suggested, I'm thinking I can add an AND and keep nesting the conditions. Something like the following:

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

However, I'm thinking there's got to be a more compact, efficient way to structure the formula, but I'm not able to visualize it...

 

[teylyn]

try nesting the second condition of the AND()

=IF($C16="Holiday","",IF(COUNTIF($C$14:$C$15,"Holiday"),if($D2="2-Day"),$D14,if(OR($D2="1-Day",$D2=""),$D15,..

It really helps to write it out. For each condition you need an action for Yes and an action for No.

is C16 = Holiday
	Yes	blank
	No	is either C14 or C14 = Holiday
		yes	is D2= 2-Day
			yes	D14
			no	is D2 = 1-Day or is D2 blank
				yes	D15
				no	something else
		no	something else

 

[estephenkim]

Hi, teylyn! I ultimately produced the following formula for this cell. I ended up adding some additional rows to the task schedule and incorporated a VLOOKUP to pull the holiday name so the formula is more robust than originally presented.

=IF($C31="Holiday",CONCATENATE("Holiday: ",VLOOKUP($B31,$H$5:$J$34,3,0)),IF(COUNTIF($C$29:$C$30,"Holiday"),IF('ESC Calendar'!$F$2="2-Day",$D29,IF(OR('ESC Calendar'!$F$2="1-Day",'ESC Calendar'!$F$2=""),$D30)),IF('ESC Calendar'!$F$2="2-Day",$D30,"")))

 

[estephenkim]

I've the completed the project and have enclosed a couple screen captures (with task descriptions redacted for privacy purposes) of the executive calendar and task schedule. Based on the meeting date and duration entered by the user, the executive calendar sources and populates the dates and task descriptions from the task schedule. I also applied conditional formatting to change the coloration of the executive decision date (teal) and any holidays (gray) as applicable. This calendar tool addresses my functional requirements and I'm really pleased with the way it turned out.

I thank you, teylyn, Peter_SSs, and DanteAmor for your contributions to and consideration of my project!!! I love using MS Excel to create useful models and tools, but as I'm a generalist by nature, I really appreciate having the support of experts like yourselves on these kinds of projects.

Best,

Stephen

 

[Peter_SSs]

This calendar tool addresses my functional requirements and I'm really pleased with the way it turned out.

Good to hear! Thanks for letting us know.