Excel - Aggregating Data in One Column Based on Values in Another Column

Question

I am trying to follow the answer here to answer my question. I have data like this:

Columns A and B are my data. I want to aggregate the data in columns A and B for every integer value in A by averaging the values in column B. So for every value in A that starts with a 17 I want an average of the 4 corresponding values in B and so on. Here is what I have tried. In column C I round column A down to the nearest integer.

In column D I use =UNIQUE(C1:C11466) to get only the unique values in C. Then in column E I am using =AVERAGEIF($C$1:$C$11466,D1#,$B$1:$B$11466) but you can see it isn't working. I'm reproducing the answer in the link (I think) but I'm not sure why it is not working.

I think there should be an easier way to do this but so far this method is the closest I've gotten. I would prefer to not use pivot tables unless there is no other way, I understand that may work also.

Answer 1

Try using the following formula here:

---

=LET(
     _Data, A1:B26,
     _ColA, INT(TAKE(_Data,,1)),
     _UniqColA, UNIQUE(_ColA),
     HSTACK(_UniqColA,BYROW(_UniqColA, LAMBDA(x, AVERAGE(FILTER(TAKE(_Data,,-1),_ColA=x))))))

---

Also if applicable one can use GROUPBY()

=GROUPBY(INT(A1:A26),B1:B26,AVERAGE,,0)

---

Edit - Here is as per OP's comments:

No worries! Yes, so also average 4 more columns C, D, E, and F (not shown in my example) in their own new columns just like column B. I didn't ask this originally because I thought I could adapt the answer for the simple case to additional columns

---

---

=LET(
     _Data,A1:C26,
     _ColA, INT(TAKE(_Data,,1)),
     _OtherCols, DROP(_Data,,1),
     _Uniq, UNIQUE(_ColA),
     _Avgs, MAKEARRAY(ROWS(_Uniq),COLUMNS(_OtherCols),LAMBDA(r,c,
            LET(x, FILTER(_OtherCols,_ColA=INDEX(_Uniq,r),0),
            AVERAGE(IFERROR(INDEX(x,,c),0))))),
     HSTACK(_Uniq,_Avgs))

---

And using GROUPBY()

=GROUPBY(INT(A1:A26),HSTACK(B1:C26),AVERAGE,,0)

---

The formula using MAKEARRAY() can be made bit shorter, I have added an additional LET() function within it to make it understandable.

---