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

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, Just a quick followup. 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

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

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

Hi Joanna, Just a quick followup. 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

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 Parentlevel 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.
Help Article Resources
Categories
Check out the Formula Handbook template!