Status Health using IF/AND Statement with Multiple Criteria

PEvansLC

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")

Bassam Khalil

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

Bassam Khalil

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

PEvansLC

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

Bassam Khalil

@PEvansLC,

you are welcome