2 Conditions on 1 Status Indicator

Stevefc
Stevefc ✭✭
edited 12/09/19 in Smartsheet Basics

I’m working on a project where I need to have to 2 conditions on 1 status indicator.

  • 1 for tracking duration against today's date:

 =IF(NETWORKDAY([Start Date]9 + 1, TODAY()) / Duration9 <= [% Complete]9, "Green", "Yellow")

  • 2. Tracking if a task is not completed on time:

=IF(AND(TODAY() >= [End Date]10 + 1, [% Complete]10 < 1), "Red", "Green")

Is it possible to combine the 2 statements in 1 formula? Or should I be taking another approach?

Tags:

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    try using another if statement

     

    =if(Criteria, true, false) would go to

    =if(criteria, if(criteria,true,false),false)

     

    This way you can have multiple conditionals stacked inside each other.

  • Stevefc
    Stevefc ✭✭

    Thanks for the update, however, my 2 IF statements have separate criteria and outcomes, would your suggest still work?  

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/02/19

    Yes, it will still work.

     

    =if([Column1]1 <= 10, if([Column1]1 > 5, "Yellow","Red"),"Green")

     

    The above will show when the value in the Column1 row 1 is:

    0-5 = "Red"

    6-10 = "Yellow"

    11+ = "Green"

    You can use this concept to even further extents, and stack as many if statements as you like. It can get rather complicated quickly though, so I would experiment with 2 if statements and see what results are given.

     

  • Stevefc
    Stevefc ✭✭

    Thank you very much for your help so far, much appreciated.

    I tried your solutions. If my first IF is true, I want a yellow status and for my second IF true a red status. IF both are false a green status. I’m just getting a red and green status no yellow, can you help?

     

    =IF(NETWORKDAY([Start Date]3 + 1, TODAY()) / Duration3 >= [% Complete]3, IF(AND(TODAY() >= [End Date]3 + 1, [% Complete]3 < 1), "Yellow", "Red"), "Green")

  • L_123
    L_123 ✭✭✭✭✭✭

    What you have right now is 

    =if(criteria, if(criteria, "yellow","red"),"green")

     

    So:

    Criteria 1 | Criteria 2         | Result

    True        | True                | Yellow

    True        | False               | Red

    False       | X                     | Green

     

    (the X means it isn't taken into account)

     

    What you want is your second IF to be in the false category of the first.

     

    =if(criteria, "yellow",if(criteria, "red","green"))

    Criteria 1 | Criteria 2         | Result

    True        | X                      | Yellow

    True        | False               | Red

    False       | True                | Green

     

     

    But there are many different ways to build this out. by switching the if statements you can use my earlier/your current format.

     

    Your current formula is:

    =IF(NETWORKDAY([Start Date]3 + 1, TODAY()) / Duration3 >= [% Complete]3, IF(AND(TODAY() >= [End Date]3 + 1, [% Complete]3 < 1), "Yellow", "Red"), "Green")

     

    ______________________________________________

    You say you aren't getting yellow, so lets follow the path to where yellow should appear in your formula

    If(Criteria,if(Criteria,"Yellow"

    However, you are able to get to both false cases. This almost certainly means there is an issue with your second criteria. 

    AND(TODAY() >= [End Date]3 + 1, [% Complete]3 < 1)

    Try to make this and statement pop true by  editing the formula

  • Stevefc
    Stevefc ✭✭

    Thanks for your quick response. I will check this out and let you know how I get on, thanks again.

  • Stevefc
    Stevefc ✭✭

    I’ve now managed to get my status in the right order using the following,

    =IF(AND(TODAY() >= [End Date]3 + 1, [% Complete]3 <= 1), "Red", IF(NETWORKDAY([Start Date]3 + 1, TODAY()) / Duration3 >= [% Complete]3, "Yellow", "Green"))

    Thank you for all your help, much appreciated!