What's wrong with my formula for setting the Health of a task?
Hello!
I'm trying to write a formula that will update the Health symbol of a task based on some of the other columns in my Smartsheet. I think I'm close, but I don't quite have it.
I want to set the Health symbol to "Yellow" if the date is within 7 days prior to the Target Completion Date OR if my % Complete column is less than the % in the YTD Time Elapsed column by 2% or less. I was able to come up with working formulas for each of these things separately, but I'm having trouble putting them together into one formula. These are the 2 pieces of formula I came up with:
=IF([Target Completion Date]@row > TODAY()  7, "Yellow")
=IF(AND([% Complete]@row > ([YTD Time Elapsed]@row  0.02), [% Complete]@row < [YTD Time Elapsed]@row), "Yellow")
The combined formula I came up with is below. It returns the Yellow symbol correctly for the % Complete part of the formula but not the date part of the formula.
=IF(AND([% Complete]@row > ([YTD Time Elapsed]@row  0.02), [% Complete]@row < [YTD Time Elapsed]@row), IF(OR([Target Completion Date]@row > TODAY()  7), "Yellow"))
Note that I plan on putting this into a complete formula where the Health symbol is
 Gray if the Status column is Not Started
 =IF((Status@row = "Not Started"), "Gray")
 Red if the Target Completion Date has passed OR % Complete is more than 2% less than the % in the YTD Time Elapsed column
 =IF(OR([Target Completion Date]@row < TODAY(), [% Complete]@row < ([YTD Time Elapsed]@row  0.02)), "Red")
 Green if none of the situations for Gray, Red, or Yellow are met (on or after the Target Start Date but at least 7 days  or 5 workdays  prior to the Target Completion Date AND % Complete equals or is greater than the % in the YTD Time Elapsed column)
Sorry for the long post, but I'd appreciate any help anyone can provide.
Thanks!
Answers

=if(status@row = "Not Started", "Gray", if(or([Target Completion Date]@row < Today(), [% Complete]@row < (YTD Time Elapsed]@row  0.02)), "Red", if(or(and([% Complete]@row > [YTD Time Elapsed]@row  0.02, [% Complete]@row < [YTD Time Elapsed]@row), [Target Completion Date]@row > TODAY()  7), "Yellow", "Green")))
I didn't test it, but it should be close. The main problem with your "Yellow" formula is that you were saying "test if A and B are both true. Then if A and B are both true, test if C is true. Then if C is true, print "Yellow". So if not C, then not "yellow". When what you needed to do was "test if A and B are both true AND test if C is true. IF A and B are both true OR if C is true, then print "Yellow".
As an aside, it seems like you are comparing percentages and days. If this is the case, there is a good chance that you are not accounting for all permutations and you will get rouge results that will require a more complex formula to account for them all. If this is the case, you might be better served by created a matrix and using index and match to get the status.

Thanks Daric,
That didn't quite work  anything where % Complete was higher than YTD Time Elapsed was also returning Yellow. But, I managed to work around it by putting Green before Yellow in the formula.
I appreciate your help!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!