#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!
-Robert
Best Answer
-
Hey @Robert B
The IFERROR function remains your friend.
=IFERROR(AVG(COLLECT({Days to assign}, {Date Assigned}, IFERROR(MONTH(@cell), 0) = 1)), 0)
Kelly
Answers
-
Hey @Robert B
The IFERROR function remains your friend.
=IFERROR(AVG(COLLECT({Days to assign}, {Date Assigned}, IFERROR(MONTH(@cell), 0) = 1)), 0)
Kelly
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!