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.


image.png

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

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 08/09/21 Answer ✓

    @Paul Newcome

    @Kayla Peacock

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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:

    image.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

    image.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

    image.png


    Some of the other parent rows seems to be working properly though.


    image.png


    image.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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@123You 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).


    然后我做了

    =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.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 08/09/21 Answer ✓

    @Paul Newcome

    @Kayla Peacock

    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")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @L@123That's brilliant! Thanks for chiming in. I would have never thought to approach it this way.

  • Beautiful! Thank you both so much!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Luke WarnerYou 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.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 08/12/21

    @Paul NewcomeI 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

    image.png

    ** 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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @L_123Hahaha. 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.

  • L_123
    L_123 ✭✭✭✭✭✭

    Yeah, that's me. :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Hi, <\/p>

Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>

=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>

Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"

Try this:<\/p>

=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/81855/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/81855/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions