NixTrader Spreadsheet for R6 – with new stuff !

nixtrader

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.

37 Comments

  • Love this spreadsheet….Plowman’s figures aren’t working though

    • You know what that means?

      Drop him

      • Would love to, but have Goodes, Gibbs & Pittard in defence. Better to have Plowman score 25 than no score at all

        • Haha, I feel your pain there my friend… Can you switch either Goodes or Gibbs to your MID bench for cover?

          • Nope…the good news is that my opponents in my leagues this week have similar problems. Might be a case of short term pain ofr long term gain. I also have Fyfe. Not touching my trades until i see teams on Fri arvo

    • He’s not a player I have considered, so I am not that familiar with him.

      Please explain what you think is wrong and I’ll have a look if there is an error.

      • Just has ##### in Last 3 avg, B/E and a few other cells. #Div/0! in the in the price change cell.
        It’s no big deal, i’m not planning on keeping, just had so many injuries & suspensions that i haven’t had a chance to get rid of him

        • He looks okay on mine. Have you typed something into his green game cell e.g. a zero or text or a even a blank space? If you clear this it might fix your problem.

          Formulas only expect an “n” if he isn’t playing, or a number if you want to add a score.

          • I’ve got the same problem, but not with same player just any player that I put in the D8 position

  • Hey Nix,

    Is it possible to have the rankings appear for when you have entered less than 7 trades?

    • OK there is a problem with line D8 – it’s only in the published version. I’ll have a look, seems like those formulas have been stuffed somehow. I should be able to fix it pretty quickly.

    • SnowyCasanova,

      Aha … you mean if you’ve cleared one of the trades. I wasn’t aware this happened, I’ll have a look.

      In the meantime, I suggest you just copy the names from your worst trade into the empty spaces so you get exact copy(copies). This will make the rankings work again.

  • Updated version added (V1.1) with changes:
    – Bug on line 8 fixed. A formula had been deleted somehow on this line. Strangely it was the formula referred to in the article (so it is important).
    – Change to trade ranking section to handle if players’ names are blank for some of the 7 trades options. The change just makes those trades have a result of -1,000 so they will always be last. It’s pretty ugly but will do the job. I’ll see if I can come up with something better for next week.

    • Actually not yet that. It is fixed and uploaded, but the link is still downloading the old version.

      So wait a few more minutes … while I sort out the link.

  • OK this seems to be working now. I changed the name to make sure it goes to right place.

    There also seems to be an issue with the hotlinks on the contents page. These may not be working properly because they are trying to point to an older version. I’ll see if I can get this working better next week.

  • Nix i have a Mac and it says it is protected and needs a password?

    • It’s not protected against opening. Some cells are protected so users can’t change formulas once it is opened.

      Mac OS shouldn’t matter. Are you using a true version of Excel or some other type of spreadsheet software that claims to support excel files?

  • Hi Nix, thanks for adding the watchlist in! Now I got no execuse for missing a trade. lol. Like the others I also have problems with the sheet. Columns Y and AA, AF-AJ, AM all got Div/0 error except for the D1 line with brendand goddard.

    • Have you downloaded the new version 1a (after the link was changed at about 1:40pm)?

      Although the problem was only on one row Defender 8 (row 12) up till then.

      Can you check you aren’t putting text or even spaces into the green column V. This will only accept an “n” (for not playing, without quotations) or a score number, any other text will cause the type of errors you describe.

  • Oh and the BE scores all look way too big. I am using OpenOffice. Maybe fixed already? But the site still serving the old file?

    • BEs are pretty high for a lot of premium players because MN is falling, it happens this time of year. That’s what drives Guns and Rookie strategy. Unless premos outperform their base point average, their price falls.

      I put Assistant Coach projections into my team, with MN 4897, I get same BEs as Assistant Coach.

      I’m happy to look at any BEs you think are wrong, but suggest you try to cross check with another BE source first.

      OpenOffice – Is this genuine MS Office Excel in the cloud, or something like Apache OpenOffice? If it isn’t real Excel it may be struggling with “array” formulas (such as the one mentioned in the article, and this formula is new this week).

      • Looks like the problem is with OpenOffice. It is showing BEs in the 300s. :)

  • Nix you are great, love the spread sheet :D hours of enjoyment and such an aid when trying to work out trades! It is much appreciated :)

  • Brilliant again! I really appreciate the hours & hours you must have put into this.

    My guess for your Spreadsheet nerd challenge:

    Is that so that when a player misses a game, we can input “n” into his score cell, which means his price changes & BE moves on to the following week.

    That’s my guess…

    • Well no, but at least you’ve had a shot.

      What you describe is something that Excel does fairly easily, but a young child would have difficulty doing.

      The formula does something that a young child can work out in their head, but Excel finds surprisingly difficult.

      I’ll send out another hint on twitter tomorrow.

  • Maybe it’s just me, but the links on the contents page all go to the My Team section. I downloaded version 1a & I’m using Windows 7 & MS Excel 2010. No big deal for me, just thought I’d mention it.

    • Actually, the My Team link works, but the other links just switch to the Tables sheet, wherever I was previously looking.

      • Yes thanks Nick, I mentioned this above. The contents links are trying to go back to an earlier version. When I renamed the sheet the links didn’t update for some reason – so they all just take you to back to the Tables sheet.

        I’ll try and sort this out for next week, I don’t think it is too much of a burden now. I was more putting these contents tags in as something for the future.

        • My bad. Not an issue for me anyway.

          As always, thanks for the great work!

  • Hey Nix,
    Thanks for all the hard work! Vastly appreciated, I tried making one of these on my own was semi helpful, nothing compared to this gem though.

    In regards to your question, does that equation count the number of games played?

    • Oops I forgot about my challenge,

      JacktheRipper – That isn’t right but you are on the right track. A child can count the number of games played. However, Excel can also count quite easily and has a number of different “count” formulas; it doesn’t need a formula as complicated as that above to do it.

      New Hint:
      The formula produces a player’s actual game score. The question is which game score it finds?

  • Is the formula there to find their LARGEST score thus far?

  • it works out the projected points from the players yearly average.. by using the amount of games they have played??

  • Yes we have a winner. Boxer is correct (or close enough).

    The formula finds the Xth last score of any player, irrespective of how many games they have missed and when they missed them.
    – If you make X = 0, it finds the last score,
    – if X = 1 it finds next last or 2nd last score,
    – if X = 2 it finds next last or 3rd last score, etc.

    So while I can ask most primary school children to look at a string of scores, mixed with gaps or ‘n’s, and just tell me the 2nd last number, Excel needs a lot of help to do this. And a lot of other spreadsheet software cannot do it at all (except by bundled IF statements that have to be rewritten after every round, and get much more complicated each week).

    Pretty cool yeah?

  • … in case anyone doesn’t know, you need a player’s last 2 & 3 scores to calculate their BEs and new price.