What's wrong with my formula for setting the Health of a task?

scisar

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!

Deric

=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.

scisar

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!