Fantasy Premier League (FPL) - Swapping players for subs depending on minutes played

MaccyW

New Member
Joined
Jan 19, 2017
Messages
7
As the FPL phone app is useless at times and doesn't even work properly when you look at the head to head teams I knocked up my own FPL spreadsheet. It looks at the API to get live data and gets the 16 players league score info for the teams. It includes who has the bonus points at that moment in time as again the FPL take ages to update after the game. It works well enough and gives you up to date scores rather than waiting the hour plus for the app to update to add any bonus points. This is a basic version of what it looks like and I just auto upload the table online so I can view it on my phone at any time.

FPL TEAM EXAMPLE.xlsx
NOPQRS
24WEEK1323/10/2022 19:20
25#HOMEPTSPTSAWAY
261TEAM 130-49TEAM 2
272TEAM 352-59TEAM 4
283TEAM 547-54TEAM 6
294TEAM 764-64TEAM 8
305TEAM 948-47TEAM 10
316TEAM 1167-63TEAM 12
327TEAM 1357-61TEAM 14
338TEAM 1575-69TEAM 16
SUBBING EXAMPLE

The only problem I have now is the swapping of players if subs are required as 1 or more of the 11 initially selected players haven't played any minutes. Again this takes an age for the FPL to update after the game has finished but it's possible to look at the player status at any moment in time and make subs depending on that status, even during live games. The goalkeepers are easy to do as it's just a one on one direct check to see if they needs changing but other 10 selected outfield players are a bit more complicated due to the formation requirements and limits.

This is an example of a team with 15 squad players with various bits of info that I think is enough to work out the team of 11 players to be counted and the are what the columns show.

A - 15 squad players
B - Player name
C - Team they play for (irrelevant)
D - Position they play
E - Fixture (irrelevant)
F - Whether the game has been played or not (I don't think this is relevant as minutes played by player will determine if they can be in the FINAL 11)
G - How many minutes the player has played
H - How many points the player has
I - Player status to either be in FINAL 11 or on BENCH

FPL TEAM EXAMPLE.xlsx
ABCDEFGH
1SQUAD #PLAYER NAMETEAMPOSITIONFIXTUREFIXTURE PLAYED?PLAYER MINS PLAYEDPOINTS
21GOALKEEPER 1CHELSEAGOALKEEPERCHELSEA - MAN UTDPLAYED00
32DEFENDER 1ARSENALDEFENDERSOUTHAMPTON - ARSENALPLAYED906
43DEFENDER 2MAN CITYDEFENDERMAN CITY - BRIGHTONPLAYED902
54DEFENDER 3LIVERPOOLDEFENDERNOTT'M FOREST - LIVERPOOLPLAYED00
65MIDFIELDER 1CHELSEAMIDFIELDERCHELSEA - MAN UTDPLAYED00
76MIDFIELDER 2MAN CITYMIDFIELDERMAN CITY - BRIGHTONPLAYED903
87MIDFIELDER 3ARSENALMIDFIELDERSOUTHAMPTON - ARSENALPLAYED902
98MIDFIELDER 4BRIGHTONMIDFIELDERMAN CITY - BRIGHTONPLAYED00
109MIDFIELDER 5MAN UTDMIDFIELDERCHELSEA - MAN UTDPLAYED903
1110FORWARD 1MAN CITYFORWARDMAN CITY - BRIGHTONPLAYED9013
1211FORWARD 2ARSENALFORWARDSOUTHAMPTON - ARSENALPLAYED901
1312GOALKEEPER 2 SUB 1LIVERPOOLGOALKEEPERNOTT'M FOREST - LIVERPOOLPLAYED906
1413FORWARD 3 SUB 2LIVERPOOLFORWARDNOTT'M FOREST - LIVERPOOLPLAYED902
1514DEFENDER 4 SUB 3NOTT'M FORESTDEFENDERNOTT'M FOREST - LIVERPOOLPLAYED901
1615DEFENDER 5 SUB 4CHELSEADEFENDERCHELSEA - MAN UTDPLAYED905
17TOTAL POINTS30
SUBBING EXAMPLE

Every squad has the following number of players in a position.
2 Goalkeepers
5 Defenders
5 Midfielders
3 Forwards

The 11 players who are in the final team has to have the minimum of the following.
1 Goalkeeper
3 Defenders
3 Midfielders
1 Forward

So ignoring the goalkeepers, all the formations allowed for Defenders, Midfielders, Forwards are as follows.
3, 4, 3
3, 5, 2
4, 3, 3
4, 4, 2
4, 5, 1
5, 3, 2
5, 4, 1

In the example table of players above it uses the formation is 3, 5, 2 and that all have played at least 1 minute in their game so it says PLAYED in column F. The only subs allowed are ones that would change the formation to any of the above. Subs who have played minutes are brought in if any of the squad players from #2 to #11 who have not yet played a minute. One thing to note is that if a sub is a FORWARD and there is a FORWARD who has played 0 minutes then the straight swap would be made first to keep the same formation. There are 3 of these in the example and the subs need to be looked at in order of #12 first then #13 then #14 and then #15. The subs don't have to match the position if the new formation created is a valid one so for example a forward could come in for a midfielder in the team above.

In the team above, squad players 1, 4, 5 & 8 haven't played so they need substituting if it's possible. That's only if there is a valid sub to be swapped with them or else they just stay in the FINAL 11. The first sub to be looked is the goalkeeper but that's easily done using a simple formula as there's only 2 goalkeepers to compare so that's SUB 1 done. The first oufield player who didn't play is #4 which is DEFENDER 3. The SUB 2 required for #4 defender has to look at first available sub which is #13 FORWARD 3. However that would mean a formation of 2,5,3 which isn't a valid formation. That means #13 is to be skipped for SUB 2 but may be valid for SUB 3 & 4 checks. The next available sub is #14 DEFENDER 4 so it's a like for like position so this is a valid sub so it is to be made. The formation is still the left as 3,5,2 for SUB 3 checks.

The next player to be subbed #5 MIDFIELDER 1 so needs to look at the first sub available which is #13 FORWARD 3 again. This sub would allowed as the new formation would be 3,4,3.

The final player to be subbed #8 MIDFIELDER 4 and the last sub available is #15 DEFENDER 5. This is allowed as the new formation would be 4,3,3. That means the final team would look like this below as all 4 subs have been used. The SUBS column H would simply say FINAL 11 or BENCH without any reordering of players as there's no real need for it to be reordered. The total team points are simply added up only if the player is in the FINAL 11 and they're highlighted in light red in both tables.

FPL TEAM EXAMPLE.xlsx
ABCDEFGHI
23SQUAD #PLAYER NAMETEAMPOSITIONFIXTUREFIXTURE PLAYED?PLAYER MINS PLAYEDPOINTSFINAL 11 /SUBS
241GOALKEEPER 1CHELSEAGOALKEEPERCHELSEA - MAN UTDPLAYED00SUB 1
252DEFENDER 1ARSENALDEFENDERSOUTHAMPTON - ARSENALPLAYED906FINAL 11
263DEFENDER 2MAN CITYDEFENDERMAN CITY - BRIGHTONPLAYED902FINAL 11
274DEFENDER 3LIVERPOOLDEFENDERNOTT'M FOREST - LIVERPOOLPLAYED00SUB 3
285MIDFIELDER 1CHELSEAMIDFIELDERCHELSEA - MAN UTDPLAYED00SUB 2
296MIDFIELDER 2MAN CITYMIDFIELDERMAN CITY - BRIGHTONPLAYED903FINAL 11
307MIDFIELDER 3ARSENALMIDFIELDERSOUTHAMPTON - ARSENALPLAYED902FINAL 11
318MIDFIELDER 4BRIGHTONMIDFIELDERMAN CITY - BRIGHTONPLAYED00SUB 4
329MIDFIELDER 5MAN UTDMIDFIELDERCHELSEA - MAN UTDPLAYED903FINAL 11
3310FORWARD 1MAN CITYFORWARDMAN CITY - BRIGHTONPLAYED9013FINAL 11
3411FORWARD 2ARSENALFORWARDSOUTHAMPTON - ARSENALPLAYED901BENCH
3512GOALKEEPER 2 SUB 1LIVERPOOLGOALKEEPERNOTT'M FOREST - LIVERPOOLPLAYED906SUB 1
3613FORWARD 3 SUB 2LIVERPOOLFORWARDNOTT'M FOREST - LIVERPOOLPLAYED902SUB 2
3714DEFENDER 4 SUB 3NOTT'M FORESTDEFENDERNOTT'M FOREST - LIVERPOOLPLAYED901SUB 3
3815DEFENDER 5 SUB 4CHELSEADEFENDERCHELSEA - MAN UTDPLAYED905SUB 4
39TOTAL POINTS44
SUBBING EXAMPLE

There are probably many permutations of subs depending on how many are required due to whether players have any minutes or not. Ignoring goalkeepers, there could be more than 3 outfield players who don't play any minutes. Those 3 are replaced using valid subs with formation limits and the others players who don't play any minutes would stay in the final team of 11 players. This can happen for example if some games for the players #2 to #11 have not yet played but players #13 to #15 have played. Players #13 to #15 would be subbed in temporarily to show the potential team but if one of #2 to #11 play then it may mean players #13 to #15 are then put back on the bench. I can work it all out in my head yet it sounds complicated to explain. I think it's an easy process if you consider the player state at that moment in time such has their match played yet, have they played any minutes etc, what position are they etc.

Again, ignoring the goalkeepers, there could be another way to look at the outfield players. Starting with SUB 2 and checking if there's a space for them. SUB 2 is a FORWARD so it first has to look at a direct swap but the 2 FORWARDs in the team have both played minutes. If one FORWARD had 0 minutes then it would be a straight swap but there isn't so now it needs checking to see if the FORWARD can replace a DEFENDER or MIDFIELDER. It would look for the first player in the list on 0 minutes and then check if they can be replaced and keep it a valid formation. In this example it would be DEFENDER 3 it would find first but that would mean a formation of 2, 5, 3 so it isn't valid. It would then find MIDFIELDER 1 next and this is a valid sub and the new formation would be 3, 4, 3. The next sub would be for SUB 3 which is a DEFENDER and this would be a straight swap for DEFENDER 3 keeping the formation 3, 4, 3. The final sub would be for SUB 4 which is a DEFENDER and it would find MIDFIELDER 4 and make the final formation 4, 3, 3.

Reading all my waffle again before posting make it look complicated but I don't tihnk it is when you work it out manually, it's just I can't think how to do it in Excel. I hope I've explained this enough without confusing people too much and I would prefer any suggestions that could use formulas in cells and extra columns aren't a problem. I got so far doing it but using multiple columns which got messy! I'm not really that experienced in VBA though I have used it to manually change code I've found to suits my needs many times in the past.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Fantasy Premier League (FPL) - Swapping players for subs depending on minutes played
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Fantasy Premier League (FPL) - Swapping players for subs depending on minutes played
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Apologies and thanks for the information as I normally find answers to my problem while searching but couldn't for this. It's my 2nd thread as my 1st was back in 2019 and you yourself offered a solution that worked (marked it yesterday as a solution) so thanks again.
 
Upvote 0

Forum statistics

Threads
1,224,814
Messages
6,181,126
Members
453,021
Latest member
Justyna P

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
Back
Top