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?

Find more posts tagged with

Accepted answers

All comments

NicoLHC

@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"))

Zach C

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?

NicoLHC

@Zach C just a general question do you want to use the data in a dashboard it could make sense to use a report?

NicoLHC

@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")
)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.