IF ERROR Formula when trying exclue zeros from average

Options
CLaughrey
CLaughrey
edited 12/09/19 in Formulas and Functions

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! 

 

 

Capture timetracking.JPG

Capture sum.JPG

Capture iferror.JPG

Comments

  • Chris McKay
    Chris McKay ✭✭✭✭✭✭
    Options

    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 smiley.

    Kind regards,

    Chris McKay

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!