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!
