NixTrader Spreadsheet for R6 – with new stuff !

Looks like a week of backline carnage, and 2 trades isn’t going to be enough.  But this spreadsheet will help you pick the best 2.

Changes this week:

• Contents page:  Since the spreadsheet is growing there is a contents page with hotlinks to assist navigation.

• My Team:  You can add your captain’s score at bottom if you want to match your actual total score for the round.

• The Bye Rounds:  An extra band is added to split the players into 3 groups based on their projected scores (90+, 70-90 & <70).  Although we still have 12 more trades before the first bye game it is good to try to keep the side reasonably balanced till then.  There is also a comment for each potential trade in the Trade Options table (column E) to show the bye impact of that trade.

• Trade Options:
  • More potential trades – you can now look at 7 potential trades at the same time (previously 5).
  • The evaluation section to the right (shaded blue) now includes a box to indicate if a player is unavailable due to long term injury (or any other reason).  This will remove that player’s average points score from the evaluation calculation.  Expect to use this when trading out a player who will miss for a while.

• I’m Stalking:  A watchlist section to add up to 24 players you might want in your team (suggested by fivepie last week).  This is selectable so you can copy your list of names into the new spreadsheet for next round.  When gurus like @Tbetta9 and @JeppaDT mention players to keep an eye on, just add those players’ names to this list.  Too easy.

• Next 5 Teams: At the far right of all the player tables there is a section to show the next 5 teams that player will face (thanks to the great @Tbetta9 for use of his fixture table).
  • At the right of these 5 teams there are 2 percentages.  These show the variation in points and 100 scores that are predicted to come from playing that string of teams.  Percentages are based on the number of points and 100s that each of those teams has conceded so far this season.  Positive percentage says that run of teams is expected to concede more points and hundreds than average; negative percentage says teams are likely to concede less points and hundreds than average.

Here is link to download spreadsheet (excel2007) –   NixTRader2013 R6 pub v1a

 

If you used the spreadsheet from previous weeks, you can just select your team block of players’ names from there and copy + paste your team into this spreadsheet.

Challenge for spreadsheet nerds:

{=IF($Games<=$X,”n”,INDEX($RoundRange,SMALL(IF(ISTEXT($RoundRange),””,COLUMN($RoundRange)),

$Games-$X)-COLUMN($RoundRange)+1))}

This formula is used lots of times in this spreadsheet.  I got it from the internet and modified it a bit so it does what I want it to do.  In truth I don’t know exactly how it works, but it sure looks impressive.

So the challenge is for spreadsheet nerds to guess / explain in one sentence what this formula does (just basic, no need to detail each step).

Hints:

1. The result is a number in the cell in which the formula is entered.  It is something quite simple, the calculation could be done by a year 2 primary school student.  It’s just Excel that finds it difficult.
2. The formula produces a player’s actual game score. The question is which game score it finds?

 

If you can answer the challenge, or have any questions, post comments below, or tweet to @NixTrader

 

EDIT: Updated version (1a) to fix:

• bug on line 8,
• change to trade ranking section to handle if players’ names are blank for some of the 7 trades options.

 

————————– notes below are mostly the same each week ——————————————-

Brief instructions:

• You only have to enter information into the green cells, the other cells are protected to prevent any errors.

• Confirm or change Magic Number estimate for the end of next two rounds.  I have put in values for MN, but if you think you know enough about MN to prefer other numbers you can change them.

• Enter your players using the drop down menu.  In the menu only the players who fit that position are named in full, other players are reduced to 3 letters.  If you used the spreadsheet from previous rounds you should be able to select and copy groups of players from there into this spreadsheet.

• For each player in your team data is pulled up automatically –

• Team and position for 2013.  If this is different to 2012, their previous team and position is also shown, or if this is their 1st year playing.

• 2013 starting price.

• Points expected based on that price (using initial magic number 5,150).  The difference in points between their 2013 base and their points average for all of 2012.  For most players who played last year this difference will be zero.

• 2012 Performance in 3 columns – average points, games played, standard deviation.

• Scores from the completed rounds in 2013 – “n” indicates they did not play.

• A projection for that player headed “Points Proj.”.  This is used as default in the calculations.  If a player has played more than 3 games this is their current average; if less than 3 games the projection is calculated from their starting price.

• There are 2 green cells to enter the actual scores (or your estimates) for your players for the next 2 rounds.  Based on these scores the last other columns calculate the averages, BEs, price change and new prices after the rounds.

• If you leave any cell blank, the calculation assumes the player gets their 2013 base score for that round – i.e. the score in column headed “Points Proj.” is used as default in the calculations.

• The player has to play 3 games for their price to change, so if the players miss games their price change won’t happen until after they play 3 games.

• If one of your players misses a round, put an “n” in the green cell for that round.  The calculations for the next round will then update to take account of this.

• At the far right of all the player tables there is a section to show the next 5 teams that player will face (thanks to the great @Tbetta9 for use of his fixture table).  At the right of these 5 teams there are 2 percentages.  These show the variation in points and 100 scores that are predicted to come from playing that string of teams.  Percentages are based on the number of points and 100s each team has conceded so far this season.  Positive percentage says this run of teams is expected to concede more points and hundreds than average; negative percentage says these teams are likely to concede less points and hundreds than average.

 

Scroll down the spreadsheet, there are more tables:

• Team Structure: A table looking at the structure of your team,

• The Bye Rounds:  A table looking at distribution of byes in your team.

• Trade Options: A table that compares different trading options.
  

• I’m Stalking: A watchlist for you to add players.  This has a place for your notes on each player at right of table (green cells).

 

FAQ:

• Why are these calculations different to other published BEs and valuations?
  • Everyone’s calculations depend on their assumptions about Magic Number, projections of future scores and how they round averages and values.  This spreadsheet should replicate any other published calculations if you make the same projections, including changing the magic number.  However, this spreadsheet is unique in that it allows you to change the estimates to do your own analysis.

• Tech talk:
  • You need a device and software that supports microsoft excel 2007 spreadsheets to be able to open and use the spreadsheet.  It won’t open on mobiles or tablets unless you have software or apps that support excel files.
  • Even if you have other software that claims to support Excel files, the file still might not work properly, as every excel formula might not be supported.  This current file uses array formulas, I expect many non-excel spreadsheets won’t support array formulas, despite supporting other excel like functions.