Nested IF's Question
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:
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.
Answers

@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"))))

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")))))
Help Article Resources
Categories
Check out the Formula Handbook template!