Getting #DIVIDE BY ZERO error message for AVG + COLLECT formula (trying to exclude zeros/blanks)

Zach C

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?

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