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

MeganJF

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!

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

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!