How can I get the Overall Project Health based upon Health Column
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.
Best Answer
-
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
Answers
-
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
-
Thank you @Genevieve P. This has helped me out tremendously.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!