Excel, Average for a specific column within table

microsoft excel

Im trying to calculate an average just for one column within a table of values.

It is like this:

Hi   Hello  Hey
1  |  2   | 3
1  |  2   | 3
1  |  2   | 3

And I'm trying to get the average for one of those columns, but which one depends from another cell that will tell me either Hi, Hello or Hey. Lets say such cell is K5

So I thought something like averageif(A1:C1;"="&K5;A2:C4). With K5 being Hi the formula returned the value 1. But when I changed a value in the Hi collumnn, having then:

Hi   Hello  Hey
1  |  2   | 3
7  |  2   | 3
1  |  2   | 3

The formula still shows me 1 (should be 3), so it is only taking into consideration the second row.

How could I have it calculate the average of A2:A4 if K5 is Hi, B2:B4 if K5 is Hello or C2:C4 if K5 is Hey?

Thank you.

Best Answer

AverageIf is not built to evaluate a whole table and return only one column. To achieve your described result, you could use something like

=AVERAGE(IF(A1:C1=K5,A2:C4,""))

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

Related Question