#DIVIDE BY ZERO Error
Hello -
I get this #DIVIDE BY ZERO error when I try to take the average on multiple criterias. Here is my formula:
=AVG(COLLECT([Target Date Variance]:[Target Date Variance], [Design Team]:[Design Team], "Experience Design", [Design Team]:[Design Team], "Prism Core", [Design Team]:[Design Team], "Platform Vertical"))
I took the average on the column itself and got an actual number. What am I doing wrong?
Best Answer
-
It looks like you want to Average the one column if three possible options are selected in another column, is that correct?
If so, you'll want to only list the second column once, then use the OR function to list your multiple options, like so:
=AVG(COLLECT([Target Date Variance]:[Target Date Variance], [Design Team]:[Design Team], OR(@cell = "Experience Design", @cell = "Prism Core", @cell = "Platform Vertical")))
If this still gives you an error, this means that the formula is either unable to find matching content for any of those three values (so make sure everything is spelled correctly), or there is an error in one of the columns you're referencing and it's being displayed in this formula as well.
Let me know if this helped!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
It looks like you want to Average the one column if three possible options are selected in another column, is that correct?
If so, you'll want to only list the second column once, then use the OR function to list your multiple options, like so:
=AVG(COLLECT([Target Date Variance]:[Target Date Variance], [Design Team]:[Design Team], OR(@cell = "Experience Design", @cell = "Prism Core", @cell = "Platform Vertical")))
If this still gives you an error, this means that the formula is either unable to find matching content for any of those three values (so make sure everything is spelled correctly), or there is an error in one of the columns you're referencing and it's being displayed in this formula as well.
Let me know if this helped!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi there!
Yes, that did the trick - thank you so much!!
Thanks,
Melissa
-
No problem! I'm glad I could help.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!