Multiple IF/AND statements

ilene_healy68056

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

Paul Newcome

Because of how nested statements work, it is simply an order of operations issue. Once the formula comes across a true value, it will not continue with the rest. A blank date cell is always considered less than TODAY() and a blank percentage will always be considered zero. That means it is getting to that specific "Red" criteria, finding it true, and then stopping. Try moving the N/A portion to the beginning of your nested IF statement.

 

ilene_healy68056

That did the trick!  Thank you so much!!

Paul Newcome

Excellent! Happy to help!