Recommended roll-up scoring?

Cory Strischek

Hello all, I have a few different methods for scoring in Smartsheets but am wondering if there is recommended documentation or formulas, specifically, for scoring a parent status indicator (RYGB ball) based on its children. Of course, this depends on the definition of each status in context, but again I am asking for recommendations.

For example:

Parent row = ??? (usually depends on % Complete vs. a goal or due date)

Child row 1 = Green

Child row 2 = Yellow

Child row 3 = Red

Child row 4 = Blue

If these are driven by % Complete we can say Blue = 100% and formulas for RYG probably indicate having missed a date or factor some %C against days since or until due date.

But if a team cannot or does not use % Complete and arrive with a key like the below, what are some ways to calculate a score for the parent?

Red = Severe or numerous mild issues with the item

Yellow = Mild issues with the item

Green = No issues with the item

Blue = Complete

NS = Not started

NA = Not applicable to my team

For the parent in this second example, would you have the user calculate the value somehow? Or, would you lock the row and calculate the value by some average of the children? Or, if there is at least one red child, parent is red. If there is no red, but one yellow, yellow. If there is no yellow or red, but one green and multiple blues, green. If all blue, blue.

Thanks

Genevieve P.

Hi @Cory Strischek

There isn't a set documentation on how to do this because the criteria for what you want to show depends on how you want to define the Parent row. There's no wrong answer here! What would make sense for your project and for your tracking? Does one red mean that the Parent should show red so you can be aware of it? Or do you care more about the majority of the rows?

I've personally seen it be done all of the ways you suggested - manually input, based on percent, based on an average of children, based on dates in the row, and then of course we could build a formula to do exactly as you said (one red = red, one yellow = yellow, one green = green, all blue = blue, otherwise blank).

However if you have child rows that contain other items (such as "Not applicable to my team") you'll just need to make sure you exclude those rows from the COUNT of Children, so if all your Child Rows are Blue but then 2 have "not applicable", it will show Blue.

Do you have a specific sheet you'd like help with? If so, could you share a screen capture (but block out sensitive data). If you define what your ideal Parent row visual would be, I'm sure we can build an IF statement to return this status automatically.

Cheers,

Genevieve