Calculate Average on Diferents Summary Tasks

[Andre Leite]

Hi everyone,

I’m trying to replicate something that used to use with Excel, using the AVG 2016 FORMULA:

Month———2016————AVG 2016——AVG 2016 FORMULA
Jan———–0,00————0,00———-=SUBTOTAL(1;F$4:F4)
Feb———–70.696,74——-35.348,37—–=SUBTOTAL(1;F$4:F5)
Mar———–52.040,94——-40.912,56—–=SUBTOTAL(1;F$4:F6)
Apr———–94.393,15——-54.282,71—–=SUBTOTAL(1;F$4:F7)
May———–59.163,59——-55.258,89—–=SUBTOTAL(1;F$4:F8)

So, I’m trying to calculate on ms project the average cost considering the total of differents summary tasks.

Steps to reach the scenario:

1) group by data1 field
2) cost1 field will be set => rollup = sum

Now I have to calculate the average of the differents cost1 summary results.

Thanks in advance.

André Leite

[Daryl Deffler]

Andre;
The answer will depend on how the project schedule is constructed.
For example, if the WBS levels are organized by month, you could create a custom project field (number) and in the Custom Fields window, set the “Calculation for task and group summary rows” to Rollup and then select Average from the drop down list. In the Custom Attributes, select Formula and then create a formula to populate the custom field based on either calculations or simply the value of another MSP field.
If the WBS is not already organized by month, you can probably do something similar to above in terms of setting up the field to average, but you may also then need to add a filter or grouping depending on your objectives. For example, if you filtered on all tasks with a Finish date in May, the custom field would show you the average of those displayed fields. But that approach may take many iterations if you’re trying to get month by month numbers over a few years. You could also apply grouping in a similar manner to organize tasks into the groups you desire. Depending on how you want to group or filter, this may also mean the creation of another customer field. For example, the new (second) custom field could simply be a formula that extracts the Month(Date) from the Finish date or maybe a concatenation of the Year(Date) followed by Month(Date) to give you a YYMM grouping.
So I know I’m not giving a specific solution, but hopefully these suggestions along with an understanding of how your schedule is constructed will help you find the solution you need.

[Author]

Posts