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")
Best 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
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
-
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.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!
-
@PEvansLC,
you are welcome
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!