Asked by:
"Average of nonzero values"
Question

I'm using Project 2010
I have a structure of nested tasks, some of which will be given a rating (think of it as a quality or risk rating, i.e. where the numbers 3,2,1 mean High.Medium,Low respectively.
I then want to generate an Average rating for the summary tasks, but accounting for the fact that only some subtasks have actually been assigned a rating. I.e. I would want the following structure
Task Rating
Summary 1 2
Subtask 1 2
Subtask 2 2
Subtask 3 <blank>
Where the blank value is ignored for the rollup calculation.
This is proving to be tricky.
Issues
 The rating column needs to be a be a number so you can do calculations.
 However blanks aren't allowed in a number field. If you use the value zero to mean "not applicable", those zeros are included in a AVERAGE() function, meaning the results are skewed by the zeros.
 I made some progress by SUM()ing the rating column, having a second "rating count" column (true if the rating is nonzero) and then calculating the summary rating as (sum of rating)/(count of rating). However this fails if there is more than one level of subtask, because while the SUM() function accumulates across all sublevels, the COUNT() values dont'.
In fact with a bit more experimentation I have found something very odd.
In a column of numbers that are manually entered, the SUM() rollup adds the numbers from all lower task levels. However, SUM() doesn't do this for a column of calculated values (like my count column), it's only summing the direct subtasks of the summary task, and not any subtasks own summary values.
Is there a way to do this, am I missing something obvious?
All replies

Yes. In Custom Fields, there is an option for Task and Group Summary rows. make that the same formula.
If that doesn't give you the result you need, you will have to use a VBA macro to get what you want.
Rod Gill
The one and only Project VBA Book

Colin, for your question, I'd use four task fields 
1) the entered rating number,
2) the calculated rating number which can be Summed,
3) a calculated rating count number that is either 1 or 0 (dether the rating score should included in the average or not)  Sum this field,
4) the result (field 2) / field 3) )  only relevant at the Summary level so make sure you select apply the formula.
Graham


Colin, for your question, I'd use four task fields 
1) the entered rating number,
2) the calculated rating number which can be Summed,
3) a calculated rating count number that is either 1 or 0 (dether the rating score should included in the average or not)  Sum this field,
4) the result (field 2) / field 3) )  only relevant at the Summary level so make sure you select apply the formula.
Graham
Thanks for this reply. After much experimentation this is basically the same conclusion I came to.
One tweak. It seems if an intermediate calculation results in an error ( a divide by zero in this case for any line where the count number is 0) Project stops calculating rollup values, however this happens silently with no error messages, all you can see is that the summary values don't change, which is pretty confusing if you haven't run into it before.
I fixed this with an IIF(Count=0, 0, ...) wrapped around the "average" calculation, so any line with a zero count doesn't attempt the SUM/COUNT calculation.

Perhaps i'm being a bit slow. This looks like a database (MS Access or SQL Server) function rather than one in Project, unless you're saying it's possible to define Project rollup calculations in SQL?
I can believe Project uses SQL under the covers but I wasn't aware this was accessible from the normal user interface.