Avg of Parent not matching up to Avg of Children

Hello,

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

http://publish.smartsheet.com/cab882d657af4f59ae5d49a83129b9d1

Thanks

Find more posts tagged with

Project Management

Comments

thadr

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

Question answered.

Mike Andreas

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.

Mike Andreas

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.