IF ERROR Formula when trying exclue zeros from average

CLaughrey

I am creating a sheet for PM weekly time tracking.  The top parent row (blue) is YTD roll up of total hours, as well as a column for average weekly hours (<---- this is my issue but I'll to that shortly).  I have a child row (green) for each quarter, and then grandchildren rows (white) for each work week.  The child rows also have roll up totals/averages for the grandchild rows (see 1st pic).

My first problem was calculating the weekly averages in the Total Hours column, excluding any zeros in the future weeks that haven't occurred yet - so I used the following formula in each child row to get an average for each quarter (see 2nd pic):

=SUM([Total Hours]3:[Total Hours]15) / (COUNT([Total Hours]3:[Total Hours]15) - COUNTIF([Total Hours]3:[Total Hours]15, 0))

This formula worked perfectly and gave me accurate averages at each child row.  My plan was to have to have an average roll up at the parent row; however, for the child rows that don't have any entries in the grandchild rows, I am getting the error #DIVIDE BY ZERO, which of course results in the same error in the parent row.  So I tried adding an IFERROR fx as follows:

=IFERROR(=SUM([Total Hours]17:[Total Hours]29) / (COUNT([Total Hours]17:[Total Hours]29) - COUNTIF([Total Hours]17:[Total Hours]29, 0)), 0)

This removed the error, but now I am getting a result of "0" even in the child rows where there was no error before (see 3rd pic).

I would greatly appreciate any alternative solutions! 

 

 

Chris McKay

Hi there,

Just remove the = before the SUM function and you should be sorted. You are getting a 0 for all values as the formulas is evaluating the whole thing as an error (even before you get to the issue of 0 rows) and therefore returning an error.

Change your last 0 to "my formula is broken" and you'll see what I mean .

Kind regards,

Chris McKay