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 likeThis is an array formula and must be confirmed with Ctrl+Shift+Enter.