Home
https://www.mediafire.com/?7drd8h6bd4ep9cq
Alright, my latest baby (linked above) is using Solver in Excel to pick a team.
Its a little convoluted so I'll try and give some instructions here
Before you use the spreadsheet properly there are a couple of setup tasks you have to do first.
First thing you have to do is install the solver add-in for Excel
1 minute tutorial here
https://www.youtube.com/watch?v=kUvitkt0M7g
Next thing to do is install solver for VBA
(you can get by without this but its required if you want my macros to work)
First go to the Developer tab in excel and click Visual Basic
Then click on Tools -> References
Then check the box next to "Solver"
Done. Easy (hopefully)
Also, when you open the sheet you may see a little box up the top saying "Enable Content"
You want to click that.
Finally, solver can be a bit CPU intensive, and I have found it works best if you close all other spreadsheets before playing around with it.
So shut down any other excel stuff you have open.
Easy, now you are ready to actually use the sheet.
This spreadsheet uses Solver - a handy tool in Excel that helps solve certain maths problems quicker than doing it yourself.
In this case, given a bunch of predicted fantasy scores, Solver is going to tell us the best combination of 17 players that fits within the salary cap.
Before we start playing, lets test the solver out.
Press Ctrl+Shift+G (or run the macro "solveit" from the Macro menu)
If that doesn't work, probably you didn't install the solver VB Reference properly.
If solver is installed, you can still run solver by going to the data tab in Excel, the "solver" and then clicking "Solve"
It might think for a few seconds (hopefully not too long), but eventually you should get this screen appear
If you get a different message. Something has done wrong somewhere. Post the error here and I'll see if I can troubleshoot.
Click OK and it will show you its favourite team. Which, if everything is working OK, will look a bit like this
Now before we go off and select a team with Shaun Fensom and Luke Kelly, lets see how we can tweak some values to get things happening
First thing is the salary cap.
At this point my sheet just calculates the best 17 scoring players, but as we need to select a team of 25 we need to allow for that.
So we subtract the cost of our 8 NPRs from our $7million cap.
What we spend on our NPRs varies. It can be as low as $1,100,000 (7 x $138k players and Joe Stimpson) but most people will want to spend a little bit more.
So come up with a value of what you think you might spend on NPRs and chuck it into cell C3.
Default value is all $143k reserves, but being realistic you'll probably want to spend more than that.
Just don't keep it set to minimum and get upset when you find an awesome 17 players, but can't find any NPRs to go with them when its time to enter your team on the site.
Second thing (and the most important bit really) is working out projected scores.
You'll notice there are 3 score columns - 2016 Avg, 2015 Avg and Projection.
2016 and 2015 Avg should be self explanatory. If the player didn't play an NRL game that year, it defaults to 15 (which is the assumed average of a player at NRL rookie price)
Projection starts off as just the average of those 2 values, but you can (and should) change these values.
This column (along with salary cap and position) is the value used by solver to determine its optimal team.
Basically solver will pick the highest total projection it can from 17 players, that keeps to salary cap and positional requirements.
So our goal is to make that value as close to what the player will score in 2017 as possible.
To see how it works, lets look at the team we had picked earlier.
A few guys there are unlikely to actually play first grade this year.
So go to the projections for Shaun Fensom, Will Smith and Luke Kelly and make them 0.
If we project a player to score 0 we can make sure that our solver won't pick them in its team
Also lets assume Damien Cook remains a bench player, so lets reduce his projection of 50 to 25.
Just overtype the value in column F
Should look like this.
Now there are probably a few other modifications you want to make to the projections there.
Maybe you want to reduce a few other players to 0. Maybe you want to tweak some projections slightly and make them just slightly higher or lower.
If you think you know what you are doing just go for it and have a play, but for the purposes of this tutorial I'll just make those 4 changes.
Now, before we can pick our new team we have to reset the sheet, otherwise bad things happen to Solver
so press Ctrl+Shift+R or run macro "resetit" and that should make all the hidden names appear again.
Now we can run our solver again and see what our new team is
Press Ctrl+Shift+G (or run macro "solveit", or run solver manually from the data tab) and we should get this
Already our team looks a little better. A few players we probably don't want in our final 17 but hopefully now you have an idea of how things work.
So to get serious with it, reset the sheet again (Ctrl+Shift+R or macro "resetit")
Then adjust some more player projections
Then run solver again.
Repeat until bored.
Basically, all you have to do is go through every single player in the game, and change their projected value from my simple average of the past 2 years. And replace it with the average score you think that player will achieve in 2017.
Hours of fun.
Obviously there is a lot of guesswork and gut feelings with this, but its pretty much what we all do every year anyway. This is just formalising the process.
Change the values all you want - give Jai Arrow the 40 point average you want from him.
Bump up Whitehead and Cleary because the assumed average of 15 from 2015 (where they didn't play) is stuffing their projection.
Knock a few points off Ryan James because he won't repeat last years monster scores.
Get Slater down to 0 because you know he is crocked.
Anyway - there are a million things you can do, just play with the projections and see what happens.
Might take a while to get things close to what you want, because my default projections are going to get a bunch of players quite wrong.
But before you get totally serious, lets just go through a few limitations of our Solver, and what you can do to get around that.
It only picks 17 players.
You can change it to pick 18, or a full squad of 25 if you want.
Just change the "Total Players" in cell C6 to 18, 19,25 or whatever. Adjust the Bench cost in C3 accordingly and go for it.
Some people like to have at least 18 good scoring players from round 1, so there is certainly some value to be had in playing with this number at times.
It doesn't account for captain.
Doubling the highest score isn't part of the calcs.
If you want you can bump up the score of Smith/Fifita by a couple points more than your actual projection to compensate.
On a similar note, if there is someone you definitely, 100% want on your team, n matter what anyone says, you can just change their projection to some ridiculous value like 500 and then they'll always be there.
Doesn't handle DPP
my Solver will only consider each player for 1 position.
If you want to pick someone who has DPP in a different position, you can overtype their position in column B
Make Tom Trbo a CTR or Koroisau a HLF for example
If you want, you can manually add in an extra row with that player having the same price and projection but a different position - but then you'll find solver will tend to pick the same player twice, once in each position.
So I find the easiest thing to do is just manually set them to one position you want to pick them in and go from their.
Solver also only handles about 200 rows of data.
My macro sorts the projections before solving, so it will only consider the first 200 names listed.
This shouldn't pose any actual problems, but if it is constantly overlooking a guy you think should be there, just make sure he is in the first 200 rows before running the Solver.
Hopefully I have made things clear.
If not just shoot me a question on this thread, and I'll see what I can do.
Alright, my latest baby (linked above) is using Solver in Excel to pick a team.
Its a little convoluted so I'll try and give some instructions here
Before you use the spreadsheet properly there are a couple of setup tasks you have to do first.
First thing you have to do is install the solver add-in for Excel
1 minute tutorial here
https://www.youtube.com/watch?v=kUvitkt0M7g
Next thing to do is install solver for VBA
(you can get by without this but its required if you want my macros to work)
First go to the Developer tab in excel and click Visual Basic
Then click on Tools -> References
Then check the box next to "Solver"
Done. Easy (hopefully)
Also, when you open the sheet you may see a little box up the top saying "Enable Content"
You want to click that.
Finally, solver can be a bit CPU intensive, and I have found it works best if you close all other spreadsheets before playing around with it.
So shut down any other excel stuff you have open.
Easy, now you are ready to actually use the sheet.
This spreadsheet uses Solver - a handy tool in Excel that helps solve certain maths problems quicker than doing it yourself.
In this case, given a bunch of predicted fantasy scores, Solver is going to tell us the best combination of 17 players that fits within the salary cap.
Before we start playing, lets test the solver out.
Press Ctrl+Shift+G (or run the macro "solveit" from the Macro menu)
If that doesn't work, probably you didn't install the solver VB Reference properly.
If solver is installed, you can still run solver by going to the data tab in Excel, the "solver" and then clicking "Solve"
It might think for a few seconds (hopefully not too long), but eventually you should get this screen appear
If you get a different message. Something has done wrong somewhere. Post the error here and I'll see if I can troubleshoot.
Click OK and it will show you its favourite team. Which, if everything is working OK, will look a bit like this
Now before we go off and select a team with Shaun Fensom and Luke Kelly, lets see how we can tweak some values to get things happening
First thing is the salary cap.
At this point my sheet just calculates the best 17 scoring players, but as we need to select a team of 25 we need to allow for that.
So we subtract the cost of our 8 NPRs from our $7million cap.
What we spend on our NPRs varies. It can be as low as $1,100,000 (7 x $138k players and Joe Stimpson) but most people will want to spend a little bit more.
So come up with a value of what you think you might spend on NPRs and chuck it into cell C3.
Default value is all $143k reserves, but being realistic you'll probably want to spend more than that.
Just don't keep it set to minimum and get upset when you find an awesome 17 players, but can't find any NPRs to go with them when its time to enter your team on the site.
Second thing (and the most important bit really) is working out projected scores.
You'll notice there are 3 score columns - 2016 Avg, 2015 Avg and Projection.
2016 and 2015 Avg should be self explanatory. If the player didn't play an NRL game that year, it defaults to 15 (which is the assumed average of a player at NRL rookie price)
Projection starts off as just the average of those 2 values, but you can (and should) change these values.
This column (along with salary cap and position) is the value used by solver to determine its optimal team.
Basically solver will pick the highest total projection it can from 17 players, that keeps to salary cap and positional requirements.
So our goal is to make that value as close to what the player will score in 2017 as possible.
To see how it works, lets look at the team we had picked earlier.
A few guys there are unlikely to actually play first grade this year.
So go to the projections for Shaun Fensom, Will Smith and Luke Kelly and make them 0.
If we project a player to score 0 we can make sure that our solver won't pick them in its team
Also lets assume Damien Cook remains a bench player, so lets reduce his projection of 50 to 25.
Just overtype the value in column F
Should look like this.
Now there are probably a few other modifications you want to make to the projections there.
Maybe you want to reduce a few other players to 0. Maybe you want to tweak some projections slightly and make them just slightly higher or lower.
If you think you know what you are doing just go for it and have a play, but for the purposes of this tutorial I'll just make those 4 changes.
Now, before we can pick our new team we have to reset the sheet, otherwise bad things happen to Solver
so press Ctrl+Shift+R or run macro "resetit" and that should make all the hidden names appear again.
Now we can run our solver again and see what our new team is
Press Ctrl+Shift+G (or run macro "solveit", or run solver manually from the data tab) and we should get this
Already our team looks a little better. A few players we probably don't want in our final 17 but hopefully now you have an idea of how things work.
So to get serious with it, reset the sheet again (Ctrl+Shift+R or macro "resetit")
Then adjust some more player projections
Then run solver again.
Repeat until bored.
Basically, all you have to do is go through every single player in the game, and change their projected value from my simple average of the past 2 years. And replace it with the average score you think that player will achieve in 2017.
Hours of fun.
Obviously there is a lot of guesswork and gut feelings with this, but its pretty much what we all do every year anyway. This is just formalising the process.
Change the values all you want - give Jai Arrow the 40 point average you want from him.
Bump up Whitehead and Cleary because the assumed average of 15 from 2015 (where they didn't play) is stuffing their projection.
Knock a few points off Ryan James because he won't repeat last years monster scores.
Get Slater down to 0 because you know he is crocked.
Anyway - there are a million things you can do, just play with the projections and see what happens.
Might take a while to get things close to what you want, because my default projections are going to get a bunch of players quite wrong.
But before you get totally serious, lets just go through a few limitations of our Solver, and what you can do to get around that.
It only picks 17 players.
You can change it to pick 18, or a full squad of 25 if you want.
Just change the "Total Players" in cell C6 to 18, 19,25 or whatever. Adjust the Bench cost in C3 accordingly and go for it.
Some people like to have at least 18 good scoring players from round 1, so there is certainly some value to be had in playing with this number at times.
It doesn't account for captain.
Doubling the highest score isn't part of the calcs.
If you want you can bump up the score of Smith/Fifita by a couple points more than your actual projection to compensate.
On a similar note, if there is someone you definitely, 100% want on your team, n matter what anyone says, you can just change their projection to some ridiculous value like 500 and then they'll always be there.
Doesn't handle DPP
my Solver will only consider each player for 1 position.
If you want to pick someone who has DPP in a different position, you can overtype their position in column B
Make Tom Trbo a CTR or Koroisau a HLF for example
If you want, you can manually add in an extra row with that player having the same price and projection but a different position - but then you'll find solver will tend to pick the same player twice, once in each position.
So I find the easiest thing to do is just manually set them to one position you want to pick them in and go from their.
Solver also only handles about 200 rows of data.
My macro sorts the projections before solving, so it will only consider the first 200 names listed.
This shouldn't pose any actual problems, but if it is constantly overlooking a guy you think should be there, just make sure he is in the first 200 rows before running the Solver.
Hopefully I have made things clear.
If not just shoot me a question on this thread, and I'll see what I can do.
