Week 3 By the numbers

Welcome to week 3 of by the numbers and I’m sure many of you will be starting to get on board with the goalkeeper selections, especially any of you who utilised last week’s choices: Courtois (18), Fabianski (14), Forster (11) and Krul (7), who all returned more than their cost as points (the general benchmark). The only negative I had was mentioning De Gea again – even with their great run of games, I’m starting to lose a bit of faith with United and last night’s dumping at the hands of MK Dons speaks volumes. I guess that I forget every year that Manchester United take a while to get going and this year seems no different.

The goalkeeping predictions are doing really well though, over the last two weeks I've predicted £34.95m of goalkeepers who have returned a very nice 75 points

Personally, on the plus side, I did manage to get over 100 points in week 2 even with RvP managing a negative score which will improve my average performance after a poor week 1.

The goalkeeper selections for this week are Hart (£11.77m), Fabianski (£6.59m), Krul (£6.15m) and Green (£1.89m). Of course the selections don’t take into account the price that you may already have a goalkeeper held at - 28.38% of managers have Courtois and many will have him at £5.94m (as do I), at this price he would also make a fine selection.

My intention going forward is to put the spreadsheet detail on-line so that you can add in the cost of your own goalkeeper to see if they are a bona-fide pick.

Here’s the graph.

So what’s this week’s column about? As promised last week, I wanted to give my readers the ultimate fantasy football spreadsheet – we’ll to be honest, it's the second best - I’ll be keeping the best one for yours truly. So what’s so good about it? Well to put it quite simply, it predicts the best team you can have any given week. It’s not omnipotent though, you’ll have to put some work in yourselves, but it’s going to change your entire approach to player selection.

There is one restriction though I’m afraid, the spreadsheet uses functionality only found in Microsoft Excel – I have tried to recreate the spreadsheet in open office, google docs etc. but they just can’t recreate the solver functionality that is required. I’d love to help everyone out so send your complaints to Apache and Google respectively.

So what is this ‘solver functionality’? This amazingly clever function allows you to find the perfect scenario for any given situation by basically trying every different possible permutation until the best one appears. How does this work for fantasy football? Well you can load a mix of five goalkeepers, fifteen defenders, fifteen midfielders and ten forwards and after waiting for a few seconds the spreadsheet will tell you the best mix you can have.

Follow this link to download the fantasy football spreadsheet

Installing solver

The first thing that you need to do in order to make the spreadsheet work is to install (for free) a little known functions within Excel - Solver functionality. The solver add-in has been available in Excel for many years and should be simple to install for those of you with a basic understanding of Excel and office programs. The following link shows you how to install it for 2010 but if you have a different version then just have a quick search for the details on-line.

http://office.microsoft.com/en-gb/excel-help/load-the-solver-add-in-HP010021570.aspx

Next Step

Now that's done, open the spreadsheet and you should see a simple page that that has blue sections to cover everything you need to do as a user. Your first job should be to change the names i.e. Goalkeeper 1, Goalkeeper 2 etc. with the name of players that you feel should score well this week. You don't have to add the maximum for each position - if you only have faith in four midfielders then put them in play and the remaining places, offering just a 1 point return for a 50 point outlay, will be ignored by the solver.

Next you need to enter the cost per player although you must make sure that you include your own held prices for players you have in your team - the final team needs to be personalised to your situation. Finally, you will need to enter the points you feel the player is worth. It is almost impossible to accurately predict a players actual points performance on any given weekend so instead give the player in each position you feel is most likely to succeed 10. Then go through all of the other players and mark them out of 10 directly compared to the most likely to do well. Don’t be afraid to use half points and I would advise against giving any player a score lower than 6 - if they're that low then they probably shouldn't be in your list in the first place.

Selecting a formation

You should see that you can select any number of players within the formation, realistically though you’re going to use either 1442, 1343, 1352, 1451. The only other option you could go for is 2553 if you want to create an entire team including substitutes (although I think that the majority of managers are happy holding three or four substitutes at 1m each).

When I use the spreadsheet on a weekly basis I prefer to run it four times, each with a different formation, keeping a record of the expected points of each and therefore knowing my ultimate team.

You then have to select your budget. If you are choosing just your 11 players and not your substitutes, you need to reduce your budget to 100 minus the cost of your substitutes.

Keeping players

Many of you will have players already who have significant discounts and regardless of whether they have a favourable match up will keep them in your team. If this is the case, all you need to do is reduce the formation positions down by the number of players you want to keep and then reduce the overall budget by the total value of any players you are retaining. You will have to be sure that you don’t include a player you're keeping within the lists of players otherwise you could well have the same player twice! If they’re in the list either replace them with another candidate or adjust their score to 1 and their price to 50 making them exceptionally poor value for money. Do not delete the line they are listed in!

Time to pick your team

So, you’ve got your collection of players, you’ve got their costs incorporated in the spreadsheet and you’ve had your best guess of their likelihood of success out of ten. The next step is simply to access the solver plug in (generally at the end of the data menu) and simply click “Solve”. You’ll note that Excel will start whirring away at the bottom left corner trying out every different mix of your players to find the one that will maximise your budget. You should then see a window pop up saying that the spreadsheet has found a solution that fits and simply press "ok" - your choice of players should now have ticks (checks) against their names.

A word of warning - if you only input expensive players, where there is no possible way of fitting them in within budget, the solver will come back and say that there is no feasible solution. If this is the case, just press cancel and find some players to include who cost a little less but with some good chances of upside.

Reset buttons

Once you have run the spreadsheet, you will need to click the first button to reset the spreadsheet before you run it again. The second button resets all the names so don't press it if you want to keep them! These buttons run Macro's so if your security level is too high then you may not have this functionality. This is easy to adjust if you search on-line.

One final point, this spreadsheet actually evolved from one I found about 10 years ago online that was based on Fantasy American Football. I don’t have a link to the original solution so I'll just say thank you to the person who wrote it for giving me the original idea.

See you all next week.