League table

[gramps]

Please help
Col A Col B Col C Col D Col E Col F
Team A Team B 3 2 Team A 1
Team C Team D 2 1 Team B 1
Team A Team C 0 0 Team C 1
Team D Team B 1 2 Team D 1
Team D Team A 2 1
Team B Team C

The above is a fixture & result table of a ‘Round Robin’ soccer competition
where columns A&B are the fixtures & column C & D are the scores of the
respective games. Column E shows the teams in a league table format and
column F is the number of wins for each team. What I need is a formula to
calculate the number of wins for each team bearing in mind they maybe either
the home or away team (either column A or Column B)
Thanks in advance
Al

 

[Mike H]

Hi,

To work out the wins use this where e1 is the team your lookinh for

=SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6>$D$1:$D$6))+SUMPRODUCT(($B$1:$B$6=E1)*($D$1:$D$6>$C$1:$C$6))

Drag down for other teams in Col E

To get draws you simply alter the formla to

=SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6=$D$1:$D$6))+SUMPRODUCT(($B$1:$B$6=E1)*($D$1:$D$6=$C$1:$C$6))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[gramps]

Thanks Mike thats exactly what I wanted.Much appreciated.
Al

 

[gramps]

Sorry to be a pain but can you tell me how to adjust the formula as at the
moment unplayed games (no scores entered in Columns c & D) are counted as
draws.
Thanks.
Al