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?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!