RYG Ball Automation - Calculate Program RYG Health based on Project Health
Hi all,
I'm looking for a formula to auto-calculate the health of a program, based on 4 components within each of the 4 projects that make up that program. Photo is below of how I have the projects & components set up, along with their RYG indication. You can see they "total up" at the bottom in Overall Program. Can anyone help with a formula here?
My qualifiers for each component's health are:
Green - Majority of components are Green
Yellow - 2+ components are Amber
Red - 1+ components are Red
I want to be able to calculate the RYG health of each component within the program, AND for those components to total up to overall program health, using the same logic listed above. Any suggestions?
Answers
-
General formula would be:
I assumes the names of your columns... Fix as needed
=IF(COUNTIF([RYG]$1:[RYG]$24,"Red",[Component]$1:[Component]$24,[Component]@row)>0,"Red", IF(COUNTIF([RYG]$1:[RYG]$24,"Yellow",[Component]$1:[Component]$24,[Component]@row)>1,"Yellow","Green"))
-
@Leibel S thank you so much for your help! I'm getting closer, but am still getting an #INCORRECT error, and I can't figure out what I'm doing wrong. Another screenshot is below, with more info. Any suggestions?
-
My bad, change the COUNTIF to COUNTIFS
-
@Leibel S YOU ARE A GENIUS! Thank you so much for this!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!