How can I get the Overall Project Health based upon Health Column

henry florez · 12/09/22

I have to create a project and I need to have overall health of the project included in the sheet. I have a column that is titled Overall Health, and another column titled Health. Health column has several tasks under it which some health status are green, a few yellow, and a few red. I would like for the Overall Health Column to look at the health column and return the overall health of the project. Mind you, in the screenshot I'm attaching, Overall Health is a column, but I'm just going to use one row in that column for the overall project health. Please help.

example_ss.png

Genevieve P. · 12/13/22

Hi @henry florez

I'd be happy to help! However the formula will need to know what you would define as the "overall health". For example, does that mean whatever colour has the majority of rows? Or if there are only Green and Red rows, would that be a Yellow health?

Here's a version of an overall formula. See if this gives you the type of summary you're looking for:

=IF(COUNTIF(Health:Health, "Red") >= (COUNTIF(Health:Health, "Yellow") + COUNTIF(Health:Health, "Green")), "Red", IF(COUNTIF(Health:Health, "Yellow") >= (COUNTIF(Health:Health, "Red") + COUNTIF(Health:Health, "Green")), "Yellow", IF((COUNTIF(Health:Health, "Red") + COUNTIF(Health:Health, "Yellow")) >= COUNTIF(Health:Health, "Green"), "Yellow", "Green")))

I'll break down what it says:

IF(COUNTIF(Health:Health, "Red") >= (COUNTIF(Health:Health, "Yellow") + COUNTIF(Health:Health, "Green")), "Red",

^ If the COUNT of Red rows is greater-than or equal-to the total number of both Yellow and Green rows, return a Red overall ball.

IF(COUNTIF(Health:Health, "Yellow") >= (COUNTIF(Health:Health, "Red") + COUNTIF(Health:Health, "Green")), "Yellow",

Same thing, but for yellow. If there are more Yellow rows than Red and Green combined, return a Yellow overall status ball.

IF((COUNTIF(Health:Health, "Red") + COUNTIF(Health:Health, "Yellow")) >= COUNTIF(Health:Health, "Green"), "Yellow",

Another Yellow statement, but this time we're looking to see if there are more Red and Yellow balls than Green balls. This could mean there are just 2 red, 2 yellow, and 2 green - then it should return YELLOW instead of Green.

I also added an = sign in there to default it to yellow if there's a tie:

"Green")))

Otherwise, it will return a Green status.

Let me know if these parameters work for you, or if you have a different definition for each overall colour!

Cheers,

Genevieve

Genevieve P. · 12/13/22