Average of multiple rows

Question

I need to calculate the average score (column 2) for a number of different values (column 1)e.g. as below, I need the average for 1, then 2, then 3, then 4.... ideally appearing on each row as a third column. The number of rows for each value varies.

1   4

1   5

1   7

2   7

2  5

2   7

2   8

3   6

3   3

4   6

4   5

I know one way would be to re-arrange the data into columns instead e.g. 1: 4 5 7 7, but I don't know how to do this.

Any help much appreciated!

Answer 1

Put this formula and drag down -

=AVERAGEIF($A$1:$A$11,A1,$B$1:$B$11)

It will populate all rows with average.

If you want to show it only once for a value -

=IF(A1=A2,"",AVERAGEIF($A$1:$A$11,A1,$B$1:$B$11))

Answer 2

Hi,

Try this. Which averages B where Col A =1

=SUMPRODUCT(($A$2:$A$20=ROW(A1))*($B$2:$B$20))/COUNTIF($A$2:$A$20,1)

Drag down 1 row and it will average B where A=2 and drag again for 3 etc

Answer 3

Thanks both, managed to get the AverageIf to work in the end.