Change RYG ball based on multiple conditions

Joanna Collins

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.

Sean Morgan

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

Joanna Collins

Thank you Sean!

Sean Morgan

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

Joanna Collins

Thank you Sean!