Multiple IF/AND statements

Options
ilene_healy68056
ilene_healy68056 ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

I am a new user and struggling with some IF statement rules to set color status.  For the most part, I am pulling from the status column called “Capability - Stage Gate Status”.  Users choose from a drop list.  In addition, I wanted to add some criteria for other scenarios, such as the Start Date is past and % is 0%; the Start date is the future, or the task is N/A.  I am using the RYGB color balls to capture the color or the short text for when something is FUT or N/A.  It seems to work fine until I add the rule for “N/A”.  Then, it just turns the color ball to Red and my newly added formula is not saved. 

Here are my rules:

Blue:  If “% Complete” is 100%, then set the color to blue

Green:  If the “Capability - Stage Gate Status” is set to "On Track" then set the status ball column to "Green”

Yellow:   If the “Capability - Stage Gate Status” is set to "Delayed/Late" then set the status ball column to "Yellow”

Red:  If the “Capability - Stage Gate Status” is set to "At Risk" then set the status ball column to "Red”

Red:  IF Start Date is less than today AND % Complete <= 0% then set the status ball column to “Red”

FUT (Future task):  If the start date is in the future (greater than today), then set the status ball column to “FUT”

N/A (Not Applicable):  If the ““Capability - Stage Gate Status” is set to ”Not Applicable”, then set the status ball to “N/A”

Here is my formula breakdown:

=IF([% Complete]@row = 1, "Blue")

IF([Capability - Stage Gate Status]@row = "On Track", "Green")

IF([Capability - Stage Gate Status]@row = "Delayed/Late", "Yellow")

IF([Capability - Stage Gate Status]@row = "At Risk", "Red")

IF(AND([Start Date]@row < TODAY(), [% Complete]@row <= 0), "Red")

IF([Start Date]@row > TODAY(), "FUT")

IF([Capability - Stage Gate Status]@row = "Not Applicable", "N/A")

Here is the formula strung together…you can see it’s missing the last rule for “N/A”.  It just disappears and the ball turns to Red.

=IF([% Complete]@row = 1, "Blue", IF([Capability - Stage Gate Status]@row = "On Track", "Green", IF([Capability - Stage Gate Status]@row = "Delayed/Late", "Yellow", IF([Capability - Stage Gate Status]@row = "At Risk", "Red", IF(AND([Start Date]@row < TODAY(), [% Complete]@row <= 0), "Red", IF([Start Date]@row > TODAY(), "FUT"))))))

I am amazed at how far I seemed to get.  Any help is greatly appreciated.

Thank you!

Ilene

2019-06-10 12_40_01-SP Automation - Internal Sites - Smartsheet.com_.png

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!