Winning combination team for soccer players

sam_owen

New Member
Joined
Sep 10, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hello All,

Thanks for having this wonderful forum and I request your help with a small challenge of mine. I am new to excel and not sure if excel can do this at all, but I give it a try.

We have a small team of players(around 30) out of which around 20 who come every week to play soccer. The team is made by splitting those who came for the came on ground to try to make two equal teams. From the data collected, I am trying to figure out if a combination of players( 2/3/4) when in one team has a better chance of winning. With 'countif' I can see who win the most, but not which team can have more wins.
Put it simply does the team have more wins if Frank & Anthony play on one side or if Jacob,Kim and Nir plays on one side. WIN in the sheet below means the the player was in the winning team and LOST vice versa.


NAMEWinsLossGame 1Game 2Game 3Game 4Game 5
Frank
2​
3​
WINWINLOSTLostLOST
Anthony
2​
3​
LostWINLOSTWinLOST
Thomas
3​
2​
WinWINWINLostLOST
Jacob
3​
2​
LostWINWINWinLOST
Shib
2​
3​
WinWINLOSTLostLOST
Marod
2​
3​
LostLOSTLOSTWinWIN
Ire
2​
3​
WinLOSTLOSTLostWIN
Jim
3​
2​
LostLOSTWINWinWIN
Nir
3​
2​
WinLOSTWINLostWIN
Kim
3​
2​
LostLOSTWINWinWIN


Thanks very much and I am curious to know if this data or additional data can show some insights.
 
Last edited by a moderator:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,302
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

For future reference, don't put your table in any code tags. I have removed them for you on this occasion.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,395
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@sam_owen Maybe something like this?
Win / Lost data in columns K >>> AX
Use column J to select players present for Team 'A' or Team 'B'
Selected players show in columns C & D and the win count displays in C2 & D2
Alter your selections until you get the balance of wins that you are happy with.
Columns F & G are 'helper' columns that stop the formulas from getting ugly.
The helper columns may be hidden.

Book1
ABCDEFGHIJKLMNOPQRS
1TEAM ATEAM BHelper AHelper BSelect TeamNAMEWinsLossGame 1Game 2Game 3Game 4Game 5
2 Win Count492999AFrank23WINWINLOSTLostLOST
3PLAYERSPLAYERS3999AAnthony23LostWINLOSTWinLOST
4FrankJacob999999Thomas32WinWINWINLostLOST
5AnthonyNir9995BJacob32LostWINWINWinLOST
6 Kim999999Shib23WinWINLOSTLostLOST
7  999999Marod23LostLOSTLOSTWinWIN
8  999999Ire23WinLOSTLOSTLostWIN
9  999999Jim32LostLOSTWINWinWIN
10  99910BNir32WinLOSTWINLostWIN
11  99911BKim32LostLOSTWINWinWIN
12  999999
Sheet3
Cell Formulas
RangeFormula
C2C2=SUMIF($J$1:$J$35,"A",$L$1:$L$35)
D2D2=SUMIF($J$1:$J$35,"B",$L$1:$L$35)
F2:F12F2=IF($J2="A",ROW(),999)
G2:G12G2=IF($J2="B",ROW(),999)
L2:L11L2=COUNTIF($N2:$AX2,"Win")
M2:M11M2=COUNTIF($N2:$AX2,"Lost")
C4:D12C4=IFERROR(INDEX($K$1:$K$34,SMALL(F$1:F$36,ROWS(C$4:C4))),"")
Cells with Data Validation
CellAllowCriteria
J2:J12ListA,B

Click the copy icon in the top l/h corner of the above XL2B Mini Sheet and you can then paste it into a worksheet to try it.
Hope that helps.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,395
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
A slight tweak that may make it easier to see players that you may wish to swap?

Book1
ABCDE
1TEAM ATEAM B
2 Win Count79
3PLAYERSPLAYERS
4Frank (2)Jacob (3)
5Anthony (2)Nir (3)
6Thomas (3)Kim (3)
7  
Sheet3
Cell Formulas
RangeFormula
C2C2=SUMIF($J$1:$J$35,"A",$L$1:$L$35)
D2D2=SUMIF($J$1:$J$35,"B",$L$1:$L$35)
C4:D7C4=IFERROR(INDEX($K$1:$K$34,SMALL(F$1:F$36,ROWS(C$4:C4)))&" ("&INDEX($L$1:$L$34,SMALL(F$1:F$36,ROWS(C$4:C4)))&")","")
 

sam_owen

New Member
Joined
Sep 10, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@Snakehips Thank you very much for helping me out and I successfully managed to cope the file you presented into excel with your instructions. The second part greatly helps to look at combinations easily which is very helpful.May I please request/clarify one more thing?

As my limited knowledge goes the formula that you developed counts the win of players into one table and then adds them up to see who makes the most wins, which is wonderful. What I am trying to see is the combinative effect. As an example, If we look at the excel file, we can see that when Frank and Shib were in same team, they lost 3 games( Game 3,4,5) and Jim & Kim won 3 games when they were in same team (Game 3,4,5). Is it possible to calculate this compatibility? What we have in reality are that some strikers score more when paired with some mid-fielders due to their chemistry. Is there a way to calculate the probability/count or so, that would be brilliant.

Thanks once again!
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,395
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Maybe this? Again using 'Helpers'. It will show basic win info to compare two partnerships, of up to five players, as selected by entering X or Y in columns K & L respectively.
You can always tweak the results it if you have other definable criteria that would better weight the result.

Book1
ABCDEFGHIJKLMNOPQRSTUVWX
1TEAM ATEAM BHelper AHelper BHelper XHelper YX PartnershipY PartnershipSelect TeamNAMEWinsLossGame 1Game 2Game 3Game 4Game 5
2 Win Count7929992999XAFrank23WINWINLOSTLostLOST
3PLAYERSPLAYERS3999999999AAnthony23LostWINLOSTWinLOST
4Frank (2)Jacob (3)4999999999AThomas32WinWINWINLostLOST
5Anthony (2)Nir (3)9995999999BJacob32LostWINWINWinLOST
6Thomas (3)Kim (3)9999996999XShib23WinWINLOSTLostLOST
7  999999999999Marod23LostLOSTLOSTWinWIN
8  999999999999Ire23WinLOSTLOSTLostWIN
9  9999999999YJim32LostLOSTWINWinWIN
10  99910999999BNir32WinLOSTWINLostWIN
11  9991199911YBKim32LostLOSTWINWinWIN
12  999999999999
13  999999999999
14  999999999999
15  999999999999
16  999999999999
17  999999999999
18  999999999999
19999999999999
20COMPARE PARTNERSHIPS999999999999
21PARTNERSHIPXY999999999999
22Games Played55999999999999
23Games Won23999999999999
24Win % 40%60%999999999999
25PLAYERSPLAYERS999999999999
26FrankJim999999999999
27ShibKim999999999999
28  999999999999
29  999999999999
30  999999999999
31999999999999
32999999999999
33999999999999
34999999999999
35
36Helper XWin11000000
37Helper XLost00111000
38Helper YWin00111000
39Helper YLost11000000
40
Sheet3
Cell Formulas
RangeFormula
C2C2=SUMIF($M$1:$M$35,"A",$O$1:$O$35)
D2D2=SUMIF($M$1:$M$35,"B",$O$1:$O$35)
F2:F34F2=IF($M2="A",ROW(),999)
G2:G34G2=IF($M2="B",ROW(),999)
H2:H34H2=IF($K2="X",ROW(),999)
I2:I34I2=IF($L2="Y",ROW(),999)
O2:O11O2=COUNTIF($Q2:$BA2,"Win")
P2:P11P2=COUNTIF($Q2:$BA2,"Lost")
C4:D18C4=IFERROR(INDEX($N$1:$N$34,SMALL(F$1:F$36,ROWS(C$4:C4)))&" ("&INDEX($O$1:$O$34,SMALL(F$1:F$36,ROWS(C$4:C4)))&")","")
C22C22=SUM(Q36:BA37)
D22D22=SUM(Q38:BA39)
C23C23=SUM(Q36:BA36)
D23D23=SUM(Q38:BA38)
C24C24=IF(C22=0,"N/A",C23/C22)
D24D24=D23/D22
C26:D30C26=IFERROR(INDEX($N$1:$N$34,SMALL(H$1:H$36,ROWS(C$4:C4))),"")
Q36:X36Q36=--(COUNTIFS($K$2:$K$34,"X",Q$2:Q$34,"Win")=COUNTIF($K$2:$K34,"X"))
Q37:X37Q37=--(COUNTIFS($K$2:$K$34,"X",Q$2:Q$34,"Lost")=COUNTIF($K$2:$K$34,"X"))
Q38:X38Q38=--(COUNTIFS($L$2:$L$34,"Y",Q$2:Q$34,"Win")=COUNTIF($L$2:$L$34,"Y"))
Q39:X39Q39=--(COUNTIFS($L$2:$L$34,"Y",Q$2:Q$34,"Lost")=COUNTIF($L$2:$L$34,"Y"))
Cells with Data Validation
CellAllowCriteria
M2:M35ListA,B
K2:K34ListX,
L2:L34ListY


Hope that helps.
 
Solution

sam_owen

New Member
Joined
Sep 10, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@Snakehips That's wonderful!

I couldn't even explain the problem clearly and you have solved it in the most amazing way! I tried this with limited data we had of team and it's exactly what I was looking for!!!!

Two queries from my side for clarification!
  1. The comparison limited to 5 players, can it be expanded to 10 or so by changing formula? Or is it an excel limitation?
  2. Is there a better way to do what I am currently doing by: Getting the data of wins from last year-Use conditional formatting to colour 'Win" and 'Lost'. Then filter and choose the main players manually with most wins and finally subject them to analysis of this sheet to find better combinations. I can imagine something in VBA, which goes cell after cell to do something. But is there anything easier than to look at around 20 players with 50 games every year ~1000 cells?
Above are just queries only and you have helped solve an ambiguous issue I had in mind for many months now!
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,395
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@Snakehips That's wonderful!

I couldn't even explain the problem clearly and you have solved it in the most amazing way! I tried this with limited data we had of team and it's exactly what I was looking for!!!!

Two queries from my side for clarification!
  1. The comparison limited to 5 players, can it be expanded to 10 or so by changing formula? Or is it an excel limitation?
  2. Is there a better way to do what I am currently doing by: Getting the data of wins from last year-Use conditional formatting to colour 'Win" and 'Lost'. Then filter and choose the main players manually with most wins and finally subject them to analysis of this sheet to find better combinations. I can imagine something in VBA, which goes cell after cell to do something. But is there anything easier than to look at around 20 players with 50 games every year ~1000 cells?
Above are just queries only and you have helped solve an ambiguous issue I had in mind for many months now!
@sam_owen You are welcome. Glad to have helped.

1. If you want to extend it to 10 players then just drag the formulas inC30:D30 down by another 5 rows. The reason I limited it to 5 was thinking that the more players you include, the less likely for it to be a combination that has played together before? If you choose a combo that has no history then the %win stat shows as N/A rather than 0% for a combo that has lost all matches when together.
2. Pass! ;)
The key is you being able to identify/define how an interpretation of your past results will aid your team selection. If you can define it then one way or another, Excel will likely have the capability of achieving it.

Good luck with the Excel and the footy.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,125
Messages
5,768,250
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top