Status Health using IF/AND Statement with Multiple Criteria

Hello, trying to create Review Status Health (R,Y,G,B) colors based on outcome of two criteria: Manger Review and Variance in days between two events. Manager Review column has (4) choices in a dropdown. (Approved, Not Started, Rejected, WIP). If Manger Review equals "Approved", Status Health should equal "Blue", does not matter what the Variance equals. If Manager Review does not equal "Approved" AND the Variance is equal or greater than 90, Status Health should equal "Green"... and so on based on the value of the variance. When I simply test for the "Green" portion of the formula, it works. When I simply test for the "Blue" portion of the formula, it works. When I add the other conditions, it fails. I have written this forwards and backwards, inserted some "OR"s, but cannot get it to work.

=IF(AND([Manager Review Status]@row <> "Approved", Variance@row < 45), "Red"), IF(AND([Manager Review Status]@row <> "Approved", Variance@row >= 45), "Yellow"), IF(AND([Manager Review Status]@row <> "Approved", Variance@row >= 90), "Green"), IF([Manager Review Status]@row = "Approved", "Blue")


Tags:

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @PEvansLC,

    please try the following formula:

    =IF([Manager Review Status]@row = "Approved", "Blue", IF(AND([Manager Review Status]@row <> "Approved",
     Variance@row >= 90), "Green", IF(AND([Manager Review Status]@row <> "Approved", Variance@row >= 45,
     Variance@row < 90), "Yellow", "Red")))
    

    the following screenshot shows the result


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @PEvansLC,

    please try the following formula:

    =IF([Manager Review Status]@row = "Approved", "Blue", IF(AND([Manager Review Status]@row <> "Approved",
     Variance@row >= 90), "Green", IF(AND([Manager Review Status]@row <> "Approved", Variance@row >= 45,
     Variance@row < 90), "Yellow", "Red")))
    

    the following screenshot shows the result


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • @Bassam Khalil Perfect! Thank you for the quick response!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 03/11/24

    @PEvansLC,

    you are welcome

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!