Formula trouble: Average score for specific word in multi-select column

I have scrubbed the community board for this answer, but was unable to find the formula I needed. Therefore I am sending out an SOS. :)

On the master sheet, I am calculating scores and an overall total average.

Example formula: =AVERAGEIF([NE: Value]:[NE: Value], <>"") -- This is working

On my metrics sheet, I would like break those down to show the averages of scores within the assigned business unit "BU".

Here is what I have tried so far:

=AVERAGEIF({Range NE:Value} <> "",{Range BU}, [item2]@row)

=AVERAGEIF({Range NE:Value} <> "",AND({Range BU}, CONTAINS("Payments"@cell)))

*Item2 = Payments

As you can see from the formula, I'm also wanting to exclude blank cells (no assigned score)

Am I even close??

Thank you in advance!

Find more posts tagged with

Formulas

Accepted answers

Genevieve P.

Hey @MeganJF

I agree with @Hollie Green! You can use an AVG(COLLECT to average a column based on multiple conditions.

An AVG(COLLECT works like this:

=AVG(COLLECT({Range to Avg}, {First Criteria Column}, "Criteria 1", {Second Criteria Column}, "Criteria 2"))

So in your case, try something like this:

=AVG(COLLECT({Range NE:Value}, {Range NE:Value}, <> "", {Range BU}, [item2]@row))

See: Average with multiple conditions

Cheers,

Genevieve

All comments

Hollie Green

AVG(Collect works well when you have multiple criteria to average

Genevieve P.

Hey @MeganJF

I agree with @Hollie Green! You can use an AVG(COLLECT to average a column based on multiple conditions.

An AVG(COLLECT works like this:

=AVG(COLLECT({Range to Avg}, {First Criteria Column}, "Criteria 1", {Second Criteria Column}, "Criteria 2"))

So in your case, try something like this:

=AVG(COLLECT({Range NE:Value}, {Range NE:Value}, <> "", {Range BU}, [item2]@row))

See: Average with multiple conditions

Cheers,

Genevieve

MeganJF

Thank you @Genevieve P. and @Hollie Green! Works perfectly!

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.