Collecting valued with IF Dependencies

I'm stumped in trying to average the values in a column ( CMO Dispo) that meets the criteria of

CMO column - "Agilent"
DOM column - 2022 year

Current non-working formula: =AVG(COLLECT([CMO Dispo (Business Days)]:[CMO Dispo (Business Days)], MFG:MFG, ="Agilent", DOM:DOM, =YEAR(2022, 1, 1)))

I'm pretty sure its not working because there are some error cells in my CMO Dispo column, but I don't know how to get it to skip cells with a error code.

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Try this:

=AVG(COLLECT([CMO Dispo (Business Days)]:[CMO Dispo (Business Days)], MFG:MFG, @cell="Agilent", DOM:DOM, IFERROR(YEAR(@cell), 0) = 2022)

eLIZo

It's still not working. The error cells are in the CMO Dispo (Business Days) column, not the year column.

I tried to use your tip in a different place in the formula, but its still not working.

=AVG(COLLECT(IFERROR([CMO Dispo (Business Days)]:[CMO Dispo (Business Days)], ""), MFG:MFG, @cell = "Agilent", DOM:DOM, IFERROR(YEAR(@cell), 0) = 2022))

Paul Newcome

Ah. You will need to get rid of that error on the source sheet then. You can wrap whatever formula you are using in that column in an IFERROR to output a blank like so:

=IFERROR(original_formula, "")

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.