Formula with multiple conditions and logic

Brian Carcich

Help! I'm trying to create a formula that automates RYG balls based on many different factors across multiple cells. I'm having trouble because the "IF" formula that I created is stopping at the first point of validation that it finds to be true and instead, I need it to keep moving on the validate all points. 

 

Here's what I'm trying to make happen in one cell:

If (Begin) has a checkbox, yellow, if (Begin) doesn't have a checkbox, blank. AND THEN if (Due) is blank, gray. AND THEN if (Actual) contains date and (Due) is blank, green. AND THEN if (Actual) is less than or equal to (Due), green. AND THEN if (Actual) is greater than (Due), red. 

Ian Swanwick

Here's an example of one of mine that might help. 

 

=IF(ISDATE([Date Sent to Customer]5), "Blue", IF(ISDATE([Date Released to SAM]5), "Green", IF(NETWORKDAYS(TODAY(), [Date Needed]5) <= 2, "Red", IF(NETWORKDAYS(TODAY(), [Date Needed]5) <= 4, "Yellow"))))

SashaR

This will need to be a nested IF statement with AND conditions. 

 

This help center article will help you build it: http://help.smartsheet.com/customer/en/portal/articles/775363-using-formulas

 

If you are having trouble, show us the formula you created and we can troubleshoot with you!

 

I would attempt to build it for you, but I dont have the time at the moment 

Brad Jones

@Ian

 

Thanks so much for posting your formula.  I had a formula I thought would work with just two conditions, but in order for it to work right I needed four conditions defined.  Your example saved my day.  With mine, i wanted to track safety bypasses, so I needed the status to stay red unless the bypass was removed, and ONLY if the bypass was removed on a day >= to the day it was installed.

 

=IF(

AND(ISBLANK(Description15), ISBLANK(Signal15), ISBLANK(Location15), ISBLANK(Reason15), ISBLANK([Installed Date]15))

, "", 

IF(ISBLANK([Removed Date]15)

, "Red",

IF(([Installed Date]15 <= [Removed Date]15)

, "Green",

IF(([Installed Date]15 > [Removed Date]15)

, "Red"

))))