Manual % complete formula in the parent row

04/29/20
Accepted

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

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

  • StevefcStevefc
    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

  • 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

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

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

  • StevefcStevefc
    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

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

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

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

Sign In or Register to comment.