Finding average in a single range with multiple criteria

[bwatkiss]

Hello, i’m trying to do a formula that gives me the average of scores from one column based on the group the individuals are in in another column. For example, let’s say I have 20 groups with four or five people in each of those groups designated in their column by their group number. In another column each of those individuals has a score, how would I then add up each of those scores for those in the same group and get the average for that group put into another column using a formula?

thank you!

 

[Fluff]

Hi & welcome to MrExcel.
Have a look at the AverageIf function.

 

[bwatkiss]

I have without success

when using this I get too many arguments error.

=AVERAGEIF(J16:J97,”=1”, J16:J97,”=2”, L16:L97)

for context the J16:J97 is the range with the group numbers, 1 and 2 are the groups, there are 20 groups but I’m just trying to get it to work on two groups first. L16:L97 is the range with th individual scores to average.

 

[Fluff]

I thought you were trying to get the average per group, not multiple groups.

 

[bwatkiss]

Yes I am trying to get the average score per group and each group has 4 or 5 scores in it. So basically I need something that calculates the mean for 20 different groups and each group has four or five members.

 

[Fluff]

In that case try

=AVERAGEIF(J16:J97,1, L16:L97)

where col J is the group & col L is the scores

 

[bwatkiss]

Yes I’ve use that and got it to work on a single group. I need a formula that does it across 20 groups.

I have 82 individuals in my list, each with their own score. With four or five individuals being in each group, 1 through 20. So I need a formula that recognizes they are in the same group and then takes that individual score adds it to each of the other member’s scores of that group and then gives the average for that group. Therefore I would have potentially 20 different averages.

 

[Fluff]

You need to repeat the formula in other cells for each group.

 

[bwatkiss]

The order of the list will change with each iteration and so that won’t work, one week they may be in group one the next week they may be in group 5.

 

[Fluff]

Sorry, but I don't understand what you are saying.
You originally asked how to get the average based on the group, which is what the formula does. Is that not what you actually want?