Overall health of parent row based on the average of green, yellow, red in children rows



I need assistance generating a formula that calculates the overall health of the parent row based on the statuses of the child rows.

For instance, if the health of the child rows have several red, yellow, green, or blue, I'd like for the overall health of the parent row to reflect that. I am struggling with this because I cannot determine if there needs to be a specific number or percentage of each color to be able to generate an accurate formula.

Any assistance you can provide is greatly appreciated.


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @ModupeT

    I'm happy to help. There isn't a standard, one can look at worst case - one red child and the parent is red. One can look at the count of each color - highest number of colors determines the parent with a hierarchy in case of ties, and one can assign values to the colors and then calculate the average. You will need to decide if the red or the green is valued at 3.

    If you can describe what you want to happen, I will work on the formula to try to accomplish.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!