Overall RYG Health Formula Based on Other Combined RYG Columns
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:
Best Answer

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
Answers

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

GGG = G
GGY = ? (Green)
GYY = Y
GGR = ? (Yellow)
GRR = ? (Yellow)
GYR = ? (Yellow)
YYY = Y
YYR = ? (Yellow)
YRR = ? (Red)
RRR = R

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

Excellent, Thanks! That worked.

Help Article Resources
Categories
Check out the Formula Handbook template!