#DIVIDE BY ZERO issue - AVG formula

Robert B

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!

-Robert

Kelly Moore

Hey @Robert B

The IFERROR function remains your friend.

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

Kelly

Kelly Moore

Hey @Robert B

The IFERROR function remains your friend.

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

Kelly

Robert B

Yes, IFERROR issues continues to haunt me!

Once again @Kelly Moore to the rescue!

Thank you!

-Robert