Nested IF's Question

Paul Newcome

@Shannon Heizenrader

I am creating a post for you so that your question and whatever solution is found is public. That way anyone having a similar issue can find help and other people are also able to weigh in on a solution that may be a better fit.

YOUR QUESTION:

Hi, Paul,I am new to the community and using formulas. I apologize if this is not the right way to ask my questions. I am looking to add a formula that is similar to one you helped with here:

Paul NewcomeApr 19, 2019

Let's see if we can't simplify this a little bit and work in your extra criteria...Working Formula: =IF(OR([% Complete]1 = 1, [% Complete]1 > 0.6), "Green", IF(AND([% Complete]1 < 0.6, [% Complete]1 > 0), IF(NETWORKDAYS(TODAY(), [End Date]13) < 5, IF(NETWORKDAYS(TODAY(), [End Date]1) <= 3, "Red", "Yellow"), "Green"), "Gray")) Simplified Formula: =IF([% Complete]1 > 0.6, "Green", IF([% Complete]1 > 0, IF(NETWORKDAYS(TODAY(), [End Date]1) < 3, "Red", IF(NETWORKDAYS(TODAY(), [End Date]1) < 5, "Yellow")), IF([% Complete]1 = 0, "Gray"))) Simplified Formula + New Criteria: =IF([% Complete]1 > 0.6, IF(NETWORKDAYS(TODAY(), [End Date]1) < 2, "Red", "Green"), IF([% Complete]1 > 0, IF(NETWORKDAYS(TODAY(), [End Date]1) < 3, "Red", IF(NETWORKDAYS(TODAY(), [End Date]1) < 5, "Yellow")), IF([% Complete]1 = 0, "Gray")))

This is the logic from the original formula with my changes. I am trying to replace the colored balls in the original with text - Completed, Future start, Active and on track, Active with some concerns, Active with major concerns. My date columns are titled Start and Finish.-If Complete% is 100% = Green - Completed - If Complete% is upper 60% = Green - Active and on track - if Complete% is under 60% and Today is less 5 days before End date = Yellow - Active with some concerns - if Complete% is under 60% and Today is less 3 days before End date OR behind End date = Red - Active with major concerns- If Complete% is 0 = Gray - Future start- Even if complete% is upper 60%, Today is less 2 days before End date = Red - Active with some concerns

I modified your formula to this:=IF([% Complete]1 = 1, "Completed", IF([% Complete]1 > 0.6, IF(NETWORKDAYS(TODAY(), [Finish]1) < 2, "Active with major concerns", "Active and on track"), IF([% Complete]1 > 0, IF(NETWORKDAYS(Start(), [Finish]1) < 3, "Active with major concerns", IF(NETWORKDAYS(Start(), [Finish]1) < 5, "Active with some concerns"), "Active and on track"), IF([% Complete]1 = 0, "Future start"))))I am still getting an unparseable result. Can you help me figure out what I am missing?Thank you!Shannon

I will review your question and post my response in the comment section.

Paul Newcome

@Shannon Heizenrader I did not check to see if this matches your criteria yet. I just moved some parenthesis around that should at least take care of your #UNPARESEABLE error. I will revisit after I have had time to compare your criteria if I have any other suggestions as to the overall build of the formula.

=IF([% Complete]1 = 1, "Completed", IF([% Complete]1 > 0.6, IF(NETWORKDAYS(TODAY(), [Finish]1) < 2, "Active with major concerns", "Active and on track"), IF([% Complete]1 > 0, IF(NETWORKDAYS(Start(), [Finish]1) < 3, "Active with major concerns", IF(NETWORKDAYS(Start(), [Finish]1) < 5, "Active with some concerns", "Active and on track")), IF([% Complete]1 = 0, "Future start"))))

Paul Newcome

Also... If the above formula still isn't working, you can give this a try as well...

=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > .6, IF(TODAY(2) > Finish@row, "Active with some concerns", "Active and on track"), IF([% Complete]@row = 0, "Future Start", IF(TODAY(3) > Finish@row, "Active with major concerns", IF(TODAY(5) > Finish@row, "Active with some concerns")))))