Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
league table? | New Users to Excel | |||
league table | Excel Worksheet Functions | |||
League table | Excel Worksheet Functions | |||
league table | Excel Discussion (Misc queries) | |||
League Table | Excel Worksheet Functions |