Overall RYG Health Formula Based on Other Combined RYG Columns

Kal

Smartsheet Community:

Does Anyone Have a RYG Formula that Looks at 3 Other RYG Columns to get the Over Health I Could Leverage?

For Example:

Scenario 1: If All 3 Columns are Green, then Overall Health is Green

Scenario 2: If All 3 Columns are Red, Then Overall Health Red

Scenario 3: If All 3 Columns are Yellow, Then Overall Health Yellow

Scenario 4: If All 2 Columns are Yellow and 1 is Green, Then Overall Health is Yellow

Here are is The Formula Column:

These are The Columns it is Referencing:

Paul Newcome

Ok. Give this a go...

=IF(COUNTIFS([Schedule Health]@row:Budget@row, @cell = "Red") = 1, "Yellow", IF(COUNTIFS([Schedule Health]@row:Budget@row, @cell = "Red") = 2, IF(COUNTIFS([Schedule Health]@row:Budget@row, @cell = "Green") = 1, "Yellow", "Red"), IF(COUNTIFS([Schedule Health]@row:Budget@row, @cell = "Red") = 3, "Red", IF(COUNTIFS([Schedule Health]@row:Budget@row, @cell = "Green")>= 2, "Green", "Yellow"))))

(1) If there is only one "Red" then output "Yellow". This takes care of GGR / GYR / YYR.

(2) If there are two "Red", if the third is "Green" then output "Yellow", otherwise output "Red". This takes care of GRR / YRR.

(3) If there are 3 "Red", then output "Red". This takes care of RRR.

(4) If there are two or more "Green" then output "Green". This takes care of GGG / GGY.

(5) All else outputs "Yellow". This takes care of GYY / YYY.

(4) GGG = G

(4) GGY = G

(5) GYY = Y

(1) GGR = Y

(2) GRR = Y

(1) GYR = Y

(5) YYY = Y

(1) YYR = Y

(2) YRR = R

(3) RRR = R