Overall RYG Health Formula Based on Other Combined RYG Columns

Kal · 11/01/22

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 · 11/01/22

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

Paul Newcome · 11/01/22

Are you able to spell out all logic or provide a matrix of sorts? Your original post is a good start, but it doesn't account for some possibilities.

GGG = G

GGY = ?

GYY = Y

GGR = ?

GRR = ?

GYR = ?

YYY = Y

YYR = ?

YRR = ?

RRR = R

Kal · 11/01/22

https://community.smartsheet.com/discussion/comment/349480#Comment_349480

GGG = G

GGY = ? (Green)

GYY = Y

GGR = ? (Yellow)

GRR = ? (Yellow)

GYR = ? (Yellow)

YYY = Y

YYR = ? (Yellow)

YRR = ? (Red)