Percentage Complete of child tasks

Hello!

I'm trying to figure out a formula to do the following:

1.) Display percentage complete of tasks marked "complete" within a child group, at the child top level

2.) Display percentage complete of child groups in the parent row.

Note that I am not using dependencies, and am using an independent "% complete" column (I can't organize my sheet like I need to when enabled). 

 

See attachment for a mock-up example. Thanks in advance!

smartsheet_percentage_complete.jpg

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Eric,

    Try this.

    1.

    =IFERROR(AVG(COLLECT(CHILDREN(); CHILDREN([email protected]); "Complete")); 0)    

    The same version but with the below changes for your and others convenience.  

    =IFERROR(AVG(COLLECT(CHILDREN(), CHILDREN([email protected]), "Complete")), 0)

    2.

    =AVG(CHILDREN())

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Thanks Andree-

    I'm in the U.S, so I used the second version you provided, but it doesn't appear to be working. I've included a screenshot w/how it looks with the formulas in place. Any further advice?

    Eric

    smartsheet_percentage_complete_withformula.jpg

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Try this.

    It should be: =IFERROR(AVG(COLLECT(CHILDREN(), CHILDREN([email protected]), "Complete")), 0)

    Best,

    Andrée

     

     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andrée Starå Thank you. This really helped me!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @George Allen

    You're more than welcome!

    Glad to hear that it helped you as well!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.