Search code examples
excelaverage

Excel how can I group by column A and calculate the average according to the value group by in column A

enter image description here

I have tried to use =Ifaverage. but be honest I have no idea about Excel. I never worked with it. thanks for helping in advance

Solution

  • Should do the trick:

    =LET(a,A1:A6,b,B1:B6,c,UNIQUE(a),HSTACK(c,MAP(c,LAMBDA(z,AVERAGE(FILTER(b,a=z))))))

    Result:

    enter image description here

    Or use UNIQUE (=UNIQUE(A1:A6) in A10) to get unique values and then use AVERAGEIF:

    =AVERAGEIF($A$1:$A$6,A10,$B$1:$B$6)

    enter image description here

    Also instead of dragging down formula above, spill is possible with:

    =AVERAGEIF($A$1:$A$6,A10#,$B$1:$B$6)

    (if A10 contains formula =UNIQUE(A1:A6), otherwise use =AVERAGEIF($A$1:$A$6,UNIQUE(A1:A6),$B$1:$B$6) )