Getting #DIVIDE BY ZERO error message for AVG + COLLECT formula (trying to exclude zeros/blanks)
I added a Curriculum Average row (blue row in the screenshot) to help roll the data up before I run an AVG(COLLECT formula from my master data sheet. I've covered up teammate information for privacy.
Currently, I am referencing the respective Region & COURSE2000 Curriculum Average in the formula below. There are COURSE2000 Curriculum Average rows with blanks and zeros, and I'm trying to exclude zeros from the formula with the following equation:
=AVG(COLLECT({Data Range 4}, {Data Range 4}, >0, {Data Range 1}, "Region 06", {Data Range 2}, "COURSE2000 Curriculum Average"))
Can you help me correct this formula?
Answers
-
@Zach C please try =AVG(COLLECT({Data Range 4}, {Data Range 4}, >0, {Data Range 4}, <> "", {Data Range 1}, "Region 06", {Data Range 2}, "COURSE2000 Curriculum Average"))
If my comment helps you, I appreciate a 💡
Kind regards
Event: Strategies for Successful Adaption
Nico | LinkedIn
CEO | Lighthouse Consultings
Lecturer in Business Information Systems | DHBW
________________________________________________________________________________
addvalue@lighthouseconsultings.com
https://www.eventbrite.de/e/driving-change-with-smartsheet-strategies-for-successful-adoption-tickets-1047813557557?utm-campaign=social&utm-content=attendeeshare&utm-medium=discovery&utm-term=listing&utm-source=cp&aff=ebdsshcopyurlWe offer Licenses - Training - Solution Engineering
🔴Certified Smartsheet Partner _______________________________________________
💯 SCALEABLE Solutions Engineered by Lighthouse Consultings
We Don’t Just Implement Smartsheet; We Revolutionize How You Manage, Plan, And Execute.
-
NicoLHC thank you!
How would I add another Region to this formula if I was interested in looking at the Average for say Region 1 and 6?
-
@Zach C just a general question do you want to use the data in a dashboard it could make sense to use a report?
If my comment helps you, I appreciate a 💡
Kind regards
Event: Strategies for Successful Adaption
Nico | LinkedIn
CEO | Lighthouse Consultings
Lecturer in Business Information Systems | DHBW
________________________________________________________________________________
addvalue@lighthouseconsultings.com
https://www.eventbrite.de/e/driving-change-with-smartsheet-strategies-for-successful-adoption-tickets-1047813557557?utm-campaign=social&utm-content=attendeeshare&utm-medium=discovery&utm-term=listing&utm-source=cp&aff=ebdsshcopyurlWe offer Licenses - Training - Solution Engineering
🔴Certified Smartsheet Partner _______________________________________________
💯 SCALEABLE Solutions Engineered by Lighthouse Consultings
We Don’t Just Implement Smartsheet; We Revolutionize How You Manage, Plan, And Execute.
-
@Zach C i think i would try this way .
=AVG(
COLLECT({Data Range 4}, {Data Range 4}, >0, {Data Range 4}, <> "", {Data Range 1}, "Region 06", {Data Range 2}, "COURSE2000 Curriculum Average"),
COLLECT({Data Range 4}, {Data Range 4}, >0, {Data Range 4}, <> "", {Data Range 1}, "Region 1", {Data Range 2}, "COURSE2000 Curriculum Average")
)If my comment helps you, I appreciate a 💡
Kind regards
Event: Strategies for Successful Adaption
Nico | LinkedIn
CEO | Lighthouse Consultings
Lecturer in Business Information Systems | DHBW
________________________________________________________________________________
addvalue@lighthouseconsultings.com
https://www.eventbrite.de/e/driving-change-with-smartsheet-strategies-for-successful-adoption-tickets-1047813557557?utm-campaign=social&utm-content=attendeeshare&utm-medium=discovery&utm-term=listing&utm-source=cp&aff=ebdsshcopyurlWe offer Licenses - Training - Solution Engineering
🔴Certified Smartsheet Partner _______________________________________________
💯 SCALEABLE Solutions Engineered by Lighthouse Consultings
We Don’t Just Implement Smartsheet; We Revolutionize How You Manage, Plan, And Execute.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!