Forum Discussion
Solved
How to calculate the average of a group starting from the data of the individual people
Hi everyone,
On "Sheet 1" I have a ranking of values relating to people (from highest to lowest) deriving from a formula: on one column I have the name and on the adjacent column the value. Line 1: Goofy | 53 / Row 2: Ciccio | 45 / Row 3: Guy | 39 / and so on.
Now, each of these 20 people belongs to a different group. In another sheet (let's call it "Sheet 2") I have a list of 20 names with the name of the group they belong to in the next column.
Starting from the values contained in that ranking, I would need to calculate the average of each group: somewhere in sheet 1 therefore I want to insert the names of the 5 groups on 5 lines and the average next to them (using the formula I am looking for): Line 1: Group 1 | 24 / Row 2: Group 2 | 13 / Row 3: Group 3 | 32 /and so on.
Basically I want to tell Excel: "Look at the 2 columns of the ranking (names and values). If that name belongs to group 1 then do the average, otherwise don't consider the data." and this for all 5 groups, so on the line below I will tell Excel "Look at the 2 columns of the ranking (names and values). If that name belongs to group 2 then do the average, otherwise do not consider the data." and so on.
P.S.: For graphic and automatic reasons I prefer to keep the list with names and group to which they belong on the other sheet. I wouldn't want to add a column next to the ranking.
I hope I have explained myself
Thanks in advance 🙂
=AVERAGE(FILTER($B$1:$B$20,IFNA(XMATCH($A$1:$A$20,E2:E10),0)))
If you have access to FILTER and XMATCH you can use this formula. The formula is in cell E12 and filled to the right in this example. Of course you can place the groups and averages in another sheet. Only for illustration i use one worksheet.
=AVERAGE(FILTER($B$1:$B$20,IFNA(XMATCH($A$1:$A$20,E2:E10),0)))
If you have access to FILTER and XMATCH you can use this formula. The formula is in cell E12 and filled to the right in this example. Of course you can place the groups and averages in another sheet. Only for illustration i use one worksheet.
- I thought there was an easier way, but that's exactly what I needed. Thank you very much!
One dumb thing I think I did, is that I disposed all the names on a column with the belonging group repeatedly on the side (2 vertical columns).
It's better to do like you did: putting groups as horizontal labels and the names under them.
