Manual % complete formula in the parent row

I am not using % complete automation in the parent row as some sub tasks do not have dates. However, using =AVG(CHILDREN()) in the parent row I get an incorrect % complete. The actual %complete should be 26%. Do I need to use a different formula allowing for task duration? 


Best Answers

  • Joanna Waligora
    Answer ✓

    Hi,

    It look like what you're after is a weighted average AVGW() and not just an average AVG().

    Assuming the columns of relevance are called "weight" and "% complete" you can try the following formula in the Parent row of the "% complete" column:

    =AVGW(CHILDREN([% complete]@row), CHILDREN([weight]@row))

  • Stevefc
    Stevefc ✭✭
    edited 04/29/20 Answer ✓

    Hi Joanna, Just a quick follow-up. I have several projects running with at least 6 or more parent rows with sub tasks. Is there a way of using the ‘children’ function for example to speed up updating my project with the AVGW function? Thank you

  • Joanna Waligora
    Answer ✓

    Not sure I understand your question. Can you try rephrasing?

    What is the specific aspect of your project tracking that you wish to speed up/automate?

    One potential approach that would spare you from manually entering the formula into Parent rows would be to add a column for the Parent-level rollup (mixing formulas and manual input in the same column can get messy). You can then also wrap the formula in an IF so you don't get errors or garbage in child rows or parent rows with no child data.

    =IF(COUNT(CHILDREN([% complete]@row))=0, "", AVGW(CHILDREN([% complete]@row), CHILDREN([weight]@row)))

Answers

  • Joanna Waligora
    Answer ✓

    Hi,

    It look like what you're after is a weighted average AVGW() and not just an average AVG().

    Assuming the columns of relevance are called "weight" and "% complete" you can try the following formula in the Parent row of the "% complete" column:

    =AVGW(CHILDREN([% complete]@row), CHILDREN([weight]@row))

  • Stevefc
    Stevefc ✭✭

    Hi Joanna, that works well, thank you, regards Steve

  • Stevefc
    Stevefc ✭✭
    edited 04/29/20 Answer ✓

    Hi Joanna, Just a quick follow-up. I have several projects running with at least 6 or more parent rows with sub tasks. Is there a way of using the ‘children’ function for example to speed up updating my project with the AVGW function? Thank you

  • Joanna Waligora
    Answer ✓

    Not sure I understand your question. Can you try rephrasing?

    What is the specific aspect of your project tracking that you wish to speed up/automate?

    One potential approach that would spare you from manually entering the formula into Parent rows would be to add a column for the Parent-level rollup (mixing formulas and manual input in the same column can get messy). You can then also wrap the formula in an IF so you don't get errors or garbage in child rows or parent rows with no child data.

    =IF(COUNT(CHILDREN([% complete]@row))=0, "", AVGW(CHILDREN([% complete]@row), CHILDREN([weight]@row)))

  • Stevefc
    Stevefc ✭✭

    Hi Joanna,

    This is what I needed to avoid manually entering the range for each parent. By using the count in the formula both the range and the weight are calculated. Thank you.    

  • KryF
    KryF ✭✭

    Hi, I have an inquiry on similar question.

    I am standardizing the format or tasks in my project plan. However, some project may not require a certain task inside the subtask. How can I disable or exclude the percentage from being included in the total percentage completion of the parent without removing the subtask from project plan template?

  • @KryF I recommend resubmitting your question as a brand new post. I was only granted the Smartsheet admin license for a very short time (over a year ago now). Without it, my hands are tied. Good luck.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!