#DIVIDE BY ZERO issue - AVG formula

Hello Smartsheet Community,

Working on a Formula to record the Average column value (numeric # of days) by a month value in another Cell (01/15/22) in same Row.

For trend analysis, ideally would like to show by each Month (Jan – Dec) an Average of #’s in a column if within the same row a Date column is posted.

=AVG(COLLECT({Days to assign}, {Date Assigned}, IFERROR(MONTH(@cell), 0) = 1))

Works perfect because I only have dates for January in the (Date Assigned) range.

As soon as I complete the formula for the other Months (2, 3, 4, etc..) I get the #DIVIDE BY ERROR result. I am assuming because I do not have any data that corresponds to the Month in question the AVERAGE equation is dividing by a zero sum.

Ideally would like the 'no data' months to show as '0', until data actually starts flowing for those months and then it would auto compute.

Thank you in advance!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!