Ms Excel : I would like to AVERAGESIFS a column excluding blank cells based on some criteria - Stack Overflow

admin管理员组

文章数量:1391987

I've two sheets namely Employee Data and Salary Average Calculation. I would like to get the average value of non blank cells from sheet 2 to sheet 1 against the respective names . I tried below formula, but got a #VALUE! error. Please note, employee data is in a table form.

Formula:

=AVERAGEIFS('Salary Average Calculation'!B$2:B$14,'Salary Average Calculation'!B$1:G$1,\[@\[Employee Name\]\],'Salary Average Calculation'!B2:B14,"\<\>")

I tried this formula but got:

#VALUE! error
=AVERAGEIFS('Salary Average Calculation'!B$2:B$14,'Salary Average Calculation'!B$1:G$1,[@[Employee Name]],'Salary Average Calculation'!B2:B14,"<>")

I've two sheets namely Employee Data and Salary Average Calculation. I would like to get the average value of non blank cells from sheet 2 to sheet 1 against the respective names . I tried below formula, but got a #VALUE! error. Please note, employee data is in a table form.

Formula:

=AVERAGEIFS('Salary Average Calculation'!B$2:B$14,'Salary Average Calculation'!B$1:G$1,\[@\[Employee Name\]\],'Salary Average Calculation'!B2:B14,"\<\>")

I tried this formula but got:

#VALUE! error
=AVERAGEIFS('Salary Average Calculation'!B$2:B$14,'Salary Average Calculation'!B$1:G$1,[@[Employee Name]],'Salary Average Calculation'!B2:B14,"<>")

• excel
• excel-formula

Share Improve this question Follow edited Mar 18 at 13:04 Mark S. 2,81911 gold badge99 silver badges2828 bronze badges asked Mar 12 at 16:09 HR- SpecializedHR- Specialized 111 silver badge22 bronze badges 5

• Sure this has been done on here - worth checking. – Solar Mike Commented Mar 12 at 16:18
• tried it , didn't work :( – HR- Specialized Commented Mar 12 at 16:29
• This one didn't work =AVERAGEIFS('Salary Average Calculation'!B$2:B$14,'Salary Average Calculation'!B$1:G$1,[@[Employee Name]],'Salary Average Calculation'!B2:B14,"\<\>") – HR- Specialized Commented Mar 12 at 16:30
• Have you tested this "\<\>" ? are you sure it works ? I know "<>" does work as I have tested that. – Solar Mike Commented Mar 12 at 16:56
• In MS365 and Excel 2021, try =AVERAGE(XLOOKUP([@[Employee Name]],'Salary Average Calculation'!$B$1:$G$1,'Salary Average Calculation'!$B$2:$G$14,"")). In older versions, try =IFNA(AVERAGE(INDEX('Salary Average Calculation'!$B$2:$G$14,,MATCH([@[Employee Name]],'Salary Average Calculation'!$B$1:$G$1,0))),""). I think AVERAGE excludes blank cells and cells containing Nothing anyway. – VBasic2008 Commented Mar 12 at 18:00

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You can do it with this formula:
=AVERAGE(INDEX(TableSalaries,,MATCH("Employee Name1",TableSalaries[#Headers])))

where TableSalaries is the name of the table from your second attachment.

When you put it in your table, you will change constant with reference to a cell with your Employee name for which you want to display the average salary, for example:
=AVERAGE(INDEX(TableSalaries,,MATCH([@[Employee Name]],TableSalaries[#Headers])))

Explanation:
- MATCH([@[Employee Name]],TableSalaries[#Headers]) is retrieving the column number in which your employee name is present
TableSalaries[#Headers] is a list of all the strings in the header of TableSalaries table
MATCH is returning index of searched element in the list

- INDEX(TableSalaries,,MATCH([@[Employee Name]],TableSalaries[#Headers])) is returning all the values from column by number, without header of course.
INDEX(array,row_number,column_number)

本文标签： Ms ExcelI would like to AVERAGESIFS a column excluding blank cells based on some criteriaStack Overflow