CODEWITHSUNDEEP
excel
Bera
Bera

Reputation: 1949

Excel - calculate average of values in one column based on another grouping column. The number of rows is not constant per group

Two columns, one with ID and one with values. I want to calculate average per ID. The number of rows per ID is not constant. What i have:

ID  Value
1   22
1   31
1   34
1   23
1   31
34  67
34  65
34  55
12  44
12  46
12  43
12  35

I want a formula which will calculate third column:

ID  Value   Average per id
1    22          28.2
1    31          28.2
1    34          28.2
1    23          28.2
1    31          28.2
34   67          62.3
34   65          62.3
34   55          62.3
12   44          42.0
12   46          42.0
12   43          42.0
12   35          42.0

I have tried AVERAGEIF function but i cant figure it out.

Upvotes: 0

Views: 2711

Answers (1)

bzimor
bzimor

Reputation: 1628

Just use these formulas:

=AVERAGEIF(A:A,A2,B:B)

or

=SUMIF(A:A,A2,B:B)/COUNTIF(A:A,A2)

Upvotes: 2

Related Questions