Finding average in a single range with multiple criteria

bwatkiss

New Member
Joined
Apr 26, 2021
Messages
7
Office Version
  1. 2011
Platform
  1. MacOS
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Have a look at the AverageIf function.
 

bwatkiss

New Member
Joined
Apr 26, 2021
Messages
7
Office Version
  1. 2011
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
I thought you were trying to get the average per group, not multiple groups.
 

bwatkiss

New Member
Joined
Apr 26, 2021
Messages
7
Office Version
  1. 2011
Platform
  1. MacOS

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
In that case try
Excel Formula:
=AVERAGEIF(J16:J97,1, L16:L97)
where col J is the group & col L is the scores
 

bwatkiss

New Member
Joined
Apr 26, 2021
Messages
7
Office Version
  1. 2011
Platform
  1. MacOS

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
You need to repeat the formula in other cells for each group.
 

bwatkiss

New Member
Joined
Apr 26, 2021
Messages
7
Office Version
  1. 2011
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,140,917
Messages
5,703,168
Members
421,279
Latest member
emzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top