none
"Average of non-zero values" RRS feed

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

    1. The rating column needs to be a be a number so you can do calculations.
    2. 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.
    3. I made some progress by SUM()ing the rating column, having a second "rating count" column (true if the rating is non-zero) 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?

    Thursday, October 25, 2012 4:14 PM

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

    Rod Gill Project Management

    Thursday, October 25, 2012 9:24 PM
    Moderator
  • 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

    Friday, October 26, 2012 9:18 AM
  • Hi,

    AVG( ) will ignore null values.

    Solution: To include null values in AVG () you have to use ISNULL( ) with a Rating column as below.

    SELECT AVG(ISNULL(Rating,0)) FROM TableName

    PS.Shakeer Hussain

    Friday, October 26, 2012 10:00 AM
  • 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.

    Wednesday, November 7, 2012 12:07 PM
  • 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.

    Wednesday, November 7, 2012 12:10 PM