Using AVG(COLLECT())

Good afternoon, I'm trying to do the equivalent of '"AVERAGEIFS". I read about using the collect formula, but I'm having an error.

The formula I'm using is:

=ABS(AVG(COLLECT({Master Sheet - 1960 - Net}, {Master Sheet - 1960 - Date}, Date@row, {Master Sheet - 1960 - Info}, [Summary Info]@row)))

I'm trying to calculate the average with a "date" and "info" condition.

I know there are values that meet both conditions, but I can't get them.

Find more posts tagged with

Formulas

collect

Accepted answers

Paul Newcome

What does this output?

=COUNTIFS({Master Sheet - 1960 - Net}, @cell <> "", {Master Sheet - 1960 - Date}, Date@row, {Master Sheet - 1960 - Info}, [Summary Info]@row)

All comments

Paul Newcome

What error exactly are you getting?

Juan Ferrero

Previously I was getting UNPARSABLE , but I fixed a spelling error.

Currently I'm getting a #divide by zero, which is odd because I know there are values that meet both conditions.

Paul Newcome

What does this output?

=COUNTIFS({Master Sheet - 1960 - Net}, @cell <> "", {Master Sheet - 1960 - Date}, Date@row, {Master Sheet - 1960 - Info}, [Summary Info]@row)

Juan Ferrero

I see now, thank you!.

The output is a number for some rows and "0" for others. Those that have 0 as output will result in #divide by zero with the previous formula.
Thank you, that's a nice way to double check

Paul Newcome

Those that output zero can be accommodated using an IFERROR function wrapped around your original formula:

=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.