Average/Roll Up Harvey Ball % Complete Symbol in Parent Row
Hello,
I have a feeling this is going to be so simple, but I cannot seem to figure out how to average the values of the Harvey Ball % Complete symbol in a parent row.
Per my screenshot, I want the Harvey Ball with the red arrow to average the values of the Harvey Balls beneath it. There is a parent/child relationship established for the rows.
In other words, I want each child row (which correlates to a task) to have its own % Complete status, and then I want the parent row to show me the % Complete overall.
Thank you!
Best Answer
-
It's not perfect, but it's never going to be when you have the values as restricted as a harvey ball. The below seems to work for my testing.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ROUNDDOWN((COUNTIF(CHILDREN(), "Quarter") + COUNTIF(CHILDREN(), "Half") * 2 + COUNTIF(CHILDREN(), "Three Quarter") * 3 + COUNTIF(CHILDREN(), "Full") * 4) / COUNT(CHILDREN())), 0, "Empty"), 1, "Quarter"), 2, "Half"), 3, "Three Quarter"), 4, "Full")
Answers
-
You are going to need a text/number helper column with the following column formula:
=IF(COUNT(CHILDREN()) = 0, IF([Symbol Column]@row = "Empty", 1, IF([Symbol Column]@row = "Quarter", 2, IF([Symbol Column]@row = "Half", 3, IF([Symbol Column]@row = "Three Quarter", 4, 5)))), ROUND(AVG(CHILDREN())))
Then in the parent rows of the Symbol Column, you would use:
=IF([Helper Column]@row = 1, "Empty", IF(Helper Column]@row = 2, "Quarter", IF([Helper Column]@row = 3, "Half", IF([Helper Column]@row = 4, "Three Quarter", "Full"))))
-
The formula in the Helper Column seems to be working, but then when I add in the formula to the parent row of the Symbol Column, I get this:
-
Ugh. I was afraid of that. So we won't be able to use that first formula as a column formula. It will have to be inserted only on the child rows.
Another option that I haven't tested yet would be to assign the "scores" within the parent row formula itself. I feel like it might get a little bulky, but here goes.
First we count how many are on each level and then multiply by the appropriate scores...
=COUNTIFS(CHILDREN(), "Empty")
=COUNTIFS(CHILDREN(), "Quarter") * 2
=COUNTIFS(CHILDREN(), "Half") * 3
=COUNTIFS(CHILDREN(), "Three Quarter") * 4
=COUNTIFS(CHILDREN(), "Full") * 5
Then we average all of these and round the result...
=ROUND(AVG(COUNTIFS(CHILDREN(), "Empty"), COUNTIFS(CHILDREN(), "Quarter") * 2, COUNTIFS(CHILDREN(), "Half") * 3, COUNTIFS(CHILDREN(), "Three Quarter") * 4, COUNTIFS(CHILDREN(), "Full") * 5))
Then we take that and put it into a nested IF to say that if the result is 1 then output "Empty", if the result is 2 then output "Quarter", so on and so forth...
=IF(ROUND(AVG(COUNTIFS(CHILDREN(), "Empty"), COUNTIFS(CHILDREN(), "Quarter") * 2, COUNTIFS(CHILDREN(), "Half") * 3, COUNTIFS(CHILDREN(), "Three Quarter") * 4, COUNTIFS(CHILDREN(), "Full") * 5)) = 1, "Empty", IF(ROUND(AVG(COUNTIFS(CHILDREN(), "Empty"), COUNTIFS(CHILDREN(), "Quarter") * 2, COUNTIFS(CHILDREN(), "Half") * 3, COUNTIFS(CHILDREN(), "Three Quarter") * 4, COUNTIFS(CHILDREN(), "Full") * 5)) = 2, "Quarter", IF(ROUND(AVG(COUNTIFS(CHILDREN(), "Empty"), COUNTIFS(CHILDREN(), "Quarter") * 2, COUNTIFS(CHILDREN(), "Half") * 3, COUNTIFS(CHILDREN(), "Three Quarter") * 4, COUNTIFS(CHILDREN(), "Full") * 5)) = 3, "Half", IF(ROUND(AVG(COUNTIFS(CHILDREN(), "Empty"), COUNTIFS(CHILDREN(), "Quarter") * 2, COUNTIFS(CHILDREN(), "Half") * 3, COUNTIFS(CHILDREN(), "Three Quarter") * 4, COUNTIFS(CHILDREN(), "Full") * 5)) = 4, "Three Quarter", "Full"))))
-
I tried your first solution of making it a child only formula and it fixed the errors but now the harvey ball in the parent row is not calculating properly.
I will try the second solution unless you have an idea.
-
Duh. Because we got rid of the average in the parent rows which feeds the second formula. Ugh. My apologies.
Give that bulky formula a shot first. If that doesn't work for you, then we can work on getting the first solution rewritten.
-
Haha no worries. I was thinking that but I wasn't sure.
I don't know if I am doing this correctly, because it's not always calculating the way I expected. I have one child that is a quarter complete but the parent row is displaying as full.
Some of the other parent rows seems to be working properly though.
-
That first one is odd. I tried it in my own sheet and was able to replicate it when I have one child row. The child row being empty or quarter makes the parent row full and the other three options make the parent row empty. And there are others that don't make sense either. Two child rows that are empty end up outputting a "Full".
@L@123 You seem to have a pretty good grasp on math and number formulas. Can you take a look at this and see if there is an issue with my logic?
I have even tried this to just output the total "Score" of the children (two child rows with "Empty")
=IF(COUNTIFS(CHILDREN(), "Empty") > 0, COUNTIFS(CHILDREN(), "Empty")) + IF(COUNTIFS(CHILDREN(), "Quarter") > 0, COUNTIFS(CHILDREN(), "Quarter") * 2) + IF(COUNTIFS(CHILDREN(), "Half") > 0, COUNTIFS(CHILDREN(), "Half") * 3) + IF(COUNTIFS(CHILDREN(), "Three Quarter") > 0, COUNTIFS(CHILDREN(), "Three Quarter") * 4) + IF(COUNTIFS(CHILDREN(), "Full") > 0, COUNTIFS(CHILDREN(), "Full") * 5)
This outputs a "2" (which is correct).
I then did
=COUNT(CHILDREN([Harvey Column]@row))
in a separate column. It also outputs "2" (also correct).
I then used
=[Harvey Column]@row / [Count Column]@row
Which output the expected "1" (great).
But if I try to combine the two formulas
=IF(COUNTIFS(CHILDREN(), "Empty") > 0, COUNTIFS(CHILDREN(), "Empty")) + IF(COUNTIFS(CHILDREN(), "Quarter") > 0, COUNTIFS(CHILDREN(), "Quarter") * 2) + IF(COUNTIFS(CHILDREN(), "Half") > 0, COUNTIFS(CHILDREN(), "Half") * 3) + IF(COUNTIFS(CHILDREN(), "Three Quarter") > 0, COUNTIFS(CHILDREN(), "Three Quarter") * 4) + IF(COUNTIFS(CHILDREN(), "Full") > 0, COUNTIFS(CHILDREN(), "Full") * 5) / COUNT(CHILDREN())
It goes back to outputting a "2" (๐คฌ).
The big formula outputs a "0" which is why it makes sense that it would output "Full". I think it is because we are getting the average from 2, 0, 0, 0, and 0 which when rounded goes down to zero so it makes sense there. But this last try of getting the "score" and then dividing by the number of children just doesn't make sense to me.
-
It's not perfect, but it's never going to be when you have the values as restricted as a harvey ball. The below seems to work for my testing.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ROUNDDOWN((COUNTIF(CHILDREN(), "Quarter") + COUNTIF(CHILDREN(), "Half") * 2 + COUNTIF(CHILDREN(), "Three Quarter") * 3 + COUNTIF(CHILDREN(), "Full") * 4) / COUNT(CHILDREN())), 0, "Empty"), 1, "Quarter"), 2, "Half"), 3, "Three Quarter"), 4, "Full")
-
@L@123 That's brilliant! Thanks for chiming in. I would have never thought to approach it this way.
-
Beautiful! Thank you both so much!!
-
@Luke Warner You changed your screenname on us. I was trying to tag you in another post that I thought you would find interesting but "@L@123" wasn't returning anything.
-
@Paul Newcome I didn't that was the smartsheet team. And they won't let me change it to anything else. Guess the @ sign I wasn't supposed to have finally annoyed them enough. I kinda liked it, showed i've been in the community since before the rule
** I figured it out... @Genevieve P. The password reset link is broken for me. All other links are working.
switched it to be closer to my old username
-
@L_123 Hahaha. Nice. I see you now have the underscore? Or at the very least I tagged the wrong "L", but now "Luke Warner" doesn't give me any options.
-
Yeah, that's me. :)
Help Article Resources
Categories
Check out the Formula Handbook template!
