Formula for summarized health indicators for all rows

MSMITH2022

We decided to use the formula below to auto color the status for each task Now we are trying to come up with a formula that would auto-calculate all of the row's statuses (Green, Red, Yellow) and display an Overall Health color - I assume it would summarize the colors and display that. Is that possible?

=IF(ISBLANK([End Date]@row), "", IF(AND([End Date]@row <= TODAY(), [% Complete]@row < 1), "Red", IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY(), [% Complete]@row < 0.25), "Yellow", IF(AND([Start Date]@row <= TODAY(), [End Date]@row >= TODAY(), [% Complete]@row >= 0.25), "Green", IF([% Complete]@row = 1, "Green", IF(AND([Start Date]@row >= TODAY(), [End Date]@row >= TODAY()), "Green", ""))))))

Paul H

I would add a helper column to Score each color then hide it, then for the Overall Health, use the average values

Score =IF(Health@row = "Red", 0, IF(Health@row = "Yellow", 1, IF(Health@row = "Green", 2)))

Overall Health =IF(AVG(Score:Score) < 0.75, "Red", IF(AVG(Score:Score) < 1.50, "Yellow", IF(AVG(Score:Score) < 2, "Green")))

MSMITH2022

Thank you very much for your help.