Excel Help

[Pancho sanza]

Say I have the following in cells a1:d3

CHI 5 Tor 2
Det 2 NY 1
Car 3 CHI 2

I need a formula that keeps a running total of goals/game so that I have updated scoring averages by team, games are in date order.

eg, prior to the Car/Chi game, Chi would have played 1 game and scored 5 goals, so they avg 5.0 goals/game

After the 3 -2 loss to carolina, they would have played 2 games , and be averaging 3.5 goals/game.

[suicidekings]

Column E: List of teams. Say CHI is in E1, DET is in E2, etc..

If it was just one column:
Column F: =AVERAGEIF(A1:A3, E1,B1:B3)

For both road and home, the simplest (although maybe not the most elegant) way would be
F1:=(SUMIF(A1:A3, E1,B1:B3)+SUMIF(C1:C3, E1,D1 : D3))/(COUNTIF(A1:A3,E1)+COUNTIF(C1:C3,E1))

If CHI was in E1, that would give you the average goals per game for them.

[Pancho sanza]

no worky


F1 =

#NAME?

[Ganchrow]

AVERAGEIF() isn't a valid excel function prior to Excel 2007.

In earlier versions of Excel, although suicidekings latter solution would certainly work, a less verbose solution (provided the data format were to remain constant) would be:

=SUMIF(A1:C3,E1,B1:D3)/COUNTIF(A1:C3,E1)

were cell E1 to contain the team of interest.

[Pancho sanza]

Made a slight modification, seems to work:

F1 = =(SUMIF($A$1:A1, $E$1,$B$1:B1)+SUMIF($C$1:C1, E1,$D$11))/(COUNTIF($A$1:A1,E1)+COUNTIF($C$1:C1,E1) )


F2 = =(SUMIF($A$1:A2, $E$1,$B$1:B2)+SUMIF($C$1:C2, E2,$D$12))/(COUNTIF($A$1:A2,E2)+COUNTIF($C$1:C2,E2) )

and so on...., that look right? That grin is really a D.

[Pancho sanza]

AVERAGEIF() isn't a valid excel function prior to Excel 2007.

In earlier versions of Excel, although suicidekings latter solution would certainly work, a less verbose solution (provided the data format were to remain constant) would be:

=SUMIF(A1:C3,E1,B1:D3)/COUNTIF(A1:C3,E1)

were cell E1 to contain the team of interest.

Had to make 1 more modification, but this works.

F1 = =(SUMIF($A$1:C1,E1,$B$11)-D1)/(COUNTIF($A$1:C1,E1)-1)

was necessary to back out the current games goals since i want to know goals per game average coming into the current game.

Many thanks to both ganchrow and suicide kings.