AVG Collect Divide by Zero error

Hello,

I am stumped again. I am looking to average data from another sheet and am getting a #Divide by Zero error, even though I believe the formula is written to ignore zeros and blanks in the data. What am I missing?

=AVG(COLLECT({Indirect Project Summary -Avg Leadtime}, {Indirect Project Summary - Executive Sponsor}, [Reporting Search Value]@row , {Indirect Project Summary - Project Status}, "Completed", {Indirect Project Summary - Project Year}, 2026, {Indirect Project Summary -Avg Leadtime}, <>"", {Indirect Project Summary -Avg Leadtime}, >0))

Find more posts tagged with

Formulas

collect

errors

Accepted answers

Paul Newcome

That error from an AVG/COLLECT leads me to believe there are no rows that meet the filtering range/criteria sets.

You can test this by using:
=COUNTIFS({Indirect Project Summary - Executive Sponsor}, [Reporting Search Value]@row, {Indirect Project Summary - Project Status}, "Completed", {Indirect Project Summary - Project Year}, 2026, {Indirect Project Summary -Avg Leadtime}, <>"", {Indirect Project Summary -Avg Leadtime}, >0)

If you believe that is incorrectly outputting a zero, you can remove all but one range/criteria set and make sure it gives you the expected number. Then, one at a time, start adding the range/criteria sets back in until you get a zero. This would be the one that is giving you trouble.

The next step from there is applying a filter with that range/criteria set to the source data to see what the source sheet is reading. If that shows zero rows, there may be a data piece that is not formatted the way you expect it to be. If that does show some rows, we would need to follow some other troubleshooting steps. In that case, screenshots would be very helpful.

All comments

Paul Newcome

That error from an AVG/COLLECT leads me to believe there are no rows that meet the filtering range/criteria sets.

LSW

@Paul Newcome you always come through with the logic. Yes, I dragged it down for other Executive Sponsors and am getting data back. I added an IFERROR return blank, and we are looking good. Thank you!

LSW

Paul Newcome

Happy to help. 👍️

Yes. That is one thing I always double check when building up a metrics sheet. If something is coming back a little weird on one row, I will apply it to more rows to see if it is a formula issue or a data issue. Then I move on tho the other troubleshooting steps. I'm glad you thought to check that as that is usually one of the easiest things to start with (that I happened to not think about this time).

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.