#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
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!