#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 46
Default League table

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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default League table

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))+S UMPRODUCT(($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))+S UMPRODUCT(($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" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 46
Default League table

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

"Mike H" wrote:

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))+S UMPRODUCT(($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))+S UMPRODUCT(($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" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 46
Default League table

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

"Mike H" wrote:

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))+S UMPRODUCT(($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))+S UMPRODUCT(($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" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
league table? jon s New Users to Excel 2 October 15th 08 06:23 PM
league table [email protected] Excel Worksheet Functions 2 September 22nd 07 03:16 PM
League table Chas Fudge Excel Worksheet Functions 1 March 10th 07 05:33 AM
league table birdoo2 Excel Discussion (Misc queries) 1 June 19th 06 12:28 PM
League Table kathleen.smith10 Excel Worksheet Functions 2 March 28th 05 02:00 PM


All times are GMT +1. The time now is 12:40 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"