averaging specific rows in multiple arrays

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i'm trying to take a total average over multiple columns that arent next to
each other for specific rows. I have a list of entries each with a label of
for example "Red Sox", "Yankees", "Orioles","Blue Jays" etc. I'm trying to
average a bunch of columns for all the "Red Sox" but the columns aren't next
to each other. For example i need columns C-E, G-I, and K-M. I have taken the
averages for all "Red Sox" within each section (C-E for example) by using the
function {=IF($B7=0,0,AVERAGE(IF(Southeast!$B$11:$B$99=$A7,G$11:I$99)))}
where B is the column containing team names, A7 is the identification "Red
Sox" and G-I are the columns of data. How do I do multiple arrays????
 
Try the following...

=AVERAGE(IF(($B$11:B$99=$A7)*(MOD(COLUMN($C$11:$M$99)-CELL("col",$C$11),4
)<>3),$C$11:$M$99))

To allow empty cells...

=AVERAGE(IF(($B$11:B$99=$A7)*(MOD(COLUMN($C$11:$M$99)-CELL("col",$C$11),4
)<>3)*($C$11:$M$99<>""),$C$11:$M$99))

To exclude zero values...

=AVERAGE(IF(($B$11:B$99=$A7)*(MOD(COLUMN($C$11:$M$99)-CELL("col",$C$11),4
)<>3)*($C$11:$M$99>0),$C$11:$M$99))

These formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.

Hope this helps!
 
Back
Top