AVERAGEIFS Returns #value!

[Alef Morais]

Hi Guys,

I trying to get the Avg results for each employees, but always return #Value!
I'm using this function: =AVERAGEIFS('Evals Report BR'!I:I,'Evals Report BR'!P:S,PowerBI!B2)
Maybe is because I selecting 4 columns in criteria range?

But if so, any function I could be using for that?

Because I have the same name employees in column 4, 3 and sometime in 2, etc..

I wanted the Excel to search for the general average of these employees regardless of the column he is.

 

[jasonb75]

Maybe is because I selecting 4 columns in criteria range?

Yes, the average and criteria range must be the same size.

You would need to use something like

=SUMPRODUCT('Evals Report BR'!I:I*('Evals Report BR'!P:S=PowerBI!B2))/SUMPRODUCT(--('Evals Report BR'!P:S=PowerBI!B2))

Set a realistic range for the formula as it will be inefficient with full columns.

 

[6StringJazzer]

The average range range has to be the same shape as the criteria range. Your average range is one column but your criteria range is 4 columns. I can't imagine how you are expecting that should work.

Your screenshot does not show the columns that are referenced in your formula so it's unclear what problem you are trying to solve.

 

[Alef Morais]

The average range range has to be the same shape as the criteria range. Your average range is one column but your criteria range is 4 columns. I can't imagine how you are expecting that should work.

Your screenshot does not show the columns that are referenced in your formula so it's unclear what problem you are trying to solve.

Sharing more screenshot.
Do you know any other function do get avg results using more 4 columns?

 

[Fluff]

How about

=LET(m,MMULT(TRANSPOSE(--('Evals Report BR'!P$2:S$1000=B2)),--('Evals Report BR'!I$2:I$1000)),SUM(m)/COUNT(m))

 

[Alef Morais]

How about

=LET(m,MMULT(TRANSPOSE(--('Evals Report BR'!P$2:S$1000=B2)),--('Evals Report BR'!I$2:I$1000)),SUM(m)/COUNT(m))

Error

Have any way to put my excel here? maybe will help more to understand.

 

[6StringJazzer]

This forum does not support file attachments but there are lots of free file-sharing services that allow you to share a link (Google Docs, Dropbox, OneDrive, etc.)

 

[Fluff]

Error

What error?

 

[Alef Morais]

Hi Guys,

The Excel are here in this link: Box

 

[Alef Morais]

What erro!va

What error?

!Value#