Change RYG ball based on multiple conditions
I am trying to change my Overall Health ball Red/Yellow/Green based on the Risk columns. If any of them are High the ball would be red, if any are medium the ball would be yellow otherwise if all are green the ball is green.
Best Answers

Hello @joannac ,
Here is a Formula I have created, based on the current view of your sheet. Please note that you may have to adjust the Column Headers in order for this work for if they have changed from the current screenshot:
The Formula I used was : =IF(OR([MARKETING RISK LEVEL]@row = "HIGH RISK", [R&D RISK LEVEL]@row = "HIGH RISK", [EQUIPMENT RISK LEVEL]@row = "HIGH RISK", [OPERATIONS RISK LEVEL]@row = "HIGH RISK", [MATERIALS RISK LEVEL]@row = "HIGH RISK", [SYSTEMS RISK LEVEL]@row = "HIGH RISK", [FINANCIALS RISK LEVEL]@row = "HIGH RISK"), "Red", IF(OR([MARKETING RISK LEVEL]@row = "MEDIUM RISK", [R&D RISK LEVEL]@row = "MEDIUM RISK", [EQUIPMENT RISK LEVEL]@row = "MEDIUM RISK", [OPERATIONS RISK LEVEL]@row = "MEDIUM RISK", [MATERIALS RISK LEVEL]@row = "MEDIUM RISK", [SYSTEMS RISK LEVEL]@row = "MEDIUM RISK", [FINANCIALS RISK LEVEL]@row = "MEDIUM RISK"), "Yellow", IF(OR([MARKETING RISK LEVEL]@row = "LOW RISK", [R&D RISK LEVEL]@row = "LOW RISK", [EQUIPMENT RISK LEVEL]@row = "LOW RISK", [OPERATIONS RISK LEVEL]@row = "LOW RISK", [MATERIALS RISK LEVEL]@row = "LOW RISK", [SYSTEMS RISK LEVEL]@row = "LOW RISK", [FINANCIALS RISK LEVEL]@row = "LOW RISK"), "Green")))
Here are the results:
As mentioned, you may have to make slight modifications for this to work in your Sheet , but I tried to base this of your Screenshot so it may allow you to just copy and paste this in.
Furthermore, this Formula could be shortened, but I like to craft my Formulas this way as when Im thinking of the conditions, and how they conditions are read logically, this is how I write them out.
Let me know if you have any questions!
Regards
Sean

Thank you Sean!
Answers

Hello @joannac ,
Here is a Formula I have created, based on the current view of your sheet. Please note that you may have to adjust the Column Headers in order for this work for if they have changed from the current screenshot:
The Formula I used was : =IF(OR([MARKETING RISK LEVEL]@row = "HIGH RISK", [R&D RISK LEVEL]@row = "HIGH RISK", [EQUIPMENT RISK LEVEL]@row = "HIGH RISK", [OPERATIONS RISK LEVEL]@row = "HIGH RISK", [MATERIALS RISK LEVEL]@row = "HIGH RISK", [SYSTEMS RISK LEVEL]@row = "HIGH RISK", [FINANCIALS RISK LEVEL]@row = "HIGH RISK"), "Red", IF(OR([MARKETING RISK LEVEL]@row = "MEDIUM RISK", [R&D RISK LEVEL]@row = "MEDIUM RISK", [EQUIPMENT RISK LEVEL]@row = "MEDIUM RISK", [OPERATIONS RISK LEVEL]@row = "MEDIUM RISK", [MATERIALS RISK LEVEL]@row = "MEDIUM RISK", [SYSTEMS RISK LEVEL]@row = "MEDIUM RISK", [FINANCIALS RISK LEVEL]@row = "MEDIUM RISK"), "Yellow", IF(OR([MARKETING RISK LEVEL]@row = "LOW RISK", [R&D RISK LEVEL]@row = "LOW RISK", [EQUIPMENT RISK LEVEL]@row = "LOW RISK", [OPERATIONS RISK LEVEL]@row = "LOW RISK", [MATERIALS RISK LEVEL]@row = "LOW RISK", [SYSTEMS RISK LEVEL]@row = "LOW RISK", [FINANCIALS RISK LEVEL]@row = "LOW RISK"), "Green")))
Here are the results:
As mentioned, you may have to make slight modifications for this to work in your Sheet , but I tried to base this of your Screenshot so it may allow you to just copy and paste this in.
Furthermore, this Formula could be shortened, but I like to craft my Formulas this way as when Im thinking of the conditions, and how they conditions are read logically, this is how I write them out.
Let me know if you have any questions!
Regards
Sean

Thank you Sean!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 360 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!