Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Avg of Parent not matching up to Avg of Children

edited 12/09/19 in Archived 2017 Posts


I'm trying to roll-up % effort at the project level based on % effort entries at the task level. I'm using the formula avg(children()) at the highest level to get the roll-up total.

As a check, I'm calculating the avg % effort at the highest parent level throughout the plan. 

I'm running into a situation where my rolled-up avg % effort (tasks level) is not equal to the avg % effort (parent level). This should be equal. 

Am I using the Children() formula incorrectly? If so, does anyone have any idea on how I can get the information I need?

So far, I can avg the % effort of the highest level parents with the criteria of ParentID and Ancestor = 1. This gets me 52% vs 46% using avg(children).




  • This seems to be the way all spreadsheets work. The more numbers you have, the better average you get. 

    Question answered.

  • The two numbers will likely be different because the start and end dates of the child tasks have a bearing on the rolled up parent percentage.  Look at the example below: According to the way the tasks lay out, there should be 63 of 65 effort days complete, or 97% for Planned Percent Complete (PPC).  However, if you average the percentages of what is actually recorded as complete, only 92% of the work is complete, Actual Percent Complete (APC).  This explains the discrepancy you're seeing.

    It took me a few days after discovering this problem to figure out what was going, but now I have it.  I had to make some additional columns, which the equation in the PPC column looks at; I can add a comment on these details if desired.

    Further, I am able to compare the PPC to the APC and format a Harvey ball to show red, yellow, green, or blue based on pre-set criteria.  Makes for a very powerful tracking and reporting system.


  • To illustrate further, I added a percentage column that calculates a parent percentage using the average children function.  In the first case, where the final task has a duration of 5 days, 80% is the result, when the APC result really should be 92% as calculated automatically by Smartsheet.  If the duration of this task is extended to 20 days, the avg(children()) still shows 80%, but Smartsheet calculates APC as 75%.  This should make it clear that the avg(children()) yields misleading results.


    Not sure if this answers your question - interested to hear if this helps.



This discussion has been closed.