FF POWER RANKINGS TEMPLATE
click the image to open the google doc
click the image to open the google doc
Read the instructions before using!!!
This is a simple tool to make your fantasy league more interesting. I put this in google docs so that it can be accessed and viewed by anybody from any computer with a browser. It will take a little bit of initial setup, but once the document is setup for your league it will be relatively easy to maintain.
I have made a standard 12 and 10 team formats as they seem to be the most popular league formats. If you have other league formats, simply add players/teams using the below steps. It's relatively straightforward, so I encourage you to try it on your own. Take a little time to understand the formulas - most of your questions will be answered with a little bit of inspection. Make sure you operate in Google Docs. Using this sheet offline in Excel will cause issues with the HLOOKUP function.
Here is a little guide on how to operate the sheet:
PLEASE DON'T REQUEST EDIT ACCESS.
I am not granting access to edit this spreadsheet through Google Sheets. You can download a copy for your own league but I will not grant edit privileges to this public template. - Go to "File > Make A Copy..." to create a duplicate of this sheet in your own Google Drive. - Go to "File > Download As" to export it in your own format. OpenDoc (.ods) format might behave better than Excel (.xlsx) format. Both 10 and 14 team versions have been created, they can be viewed using the tabs below.
TO UPDATE SCORES:
Weekly scores must be manually entered into a new row in the sheet
"WEEK #" (located under the "league name") must be manually updated in order to correctly update the rest of the calculations for the current week - Wins for each team must be updated each week
Points Against must be manually updated, however, this is not used in the formula for Power Score so you may ignore it if you please.
Everything else is automatic.
ADDING/REMOVING PLAYERS OR TEAMS:
It probably requires a little bit of excel experience to really understand what's going on (not that you don't have it), but now's a perfect time to learn.
Cut (don't copy) the "Tier Determination" and "Lions & Lambs" tables from beneath the main table and paste it to far to the right side of the main power ranking table. This is just to get it out of the way. Cutting and pasting won't change cell references, copy and pasting will.
Unmerge the merged cells in the power ranking tables. Pay attention to how the formulas work on these merged cells and tweak them to accommodate all the players in your own league.
Add the necessary columns for additional players you need.
Merge and move everything back to the original layout and correct the necessary formulas. Remember, cut/paste. Don't copy/paste.
It will take a while to tweak it for your league, that's just the way these things go. This took me a few hours to set up the way I wanted it, but I'm also an experienced excel user. Any excel question can be answered with a google search (google docs use excel syntax for the most part).
ADDING A WEEK:
For a new week, duplicate the current sheet and update the "WEEK #" title underneath your LEAGUE NAME. Make sure you update any week references to be the current week in the "rank change" row - this row should reference the sheet from the previous week to give you a ranking delta.
Adding a week is as simple as selecting the row of the current week (e.g. week 2, row 21), right clicking and selecting "insert 1 below".
If you do this correctly you won't have to do anything else, formulas will automatically update.
CONDITIONAL FORMATTING:
This sheet contains a few instances of conditional formatting. I encourage you to inspect these instances to understand how they work.
In the ranking row, change the conditional formatting to go red when a value is equal to the number of players in your league. In my league the value is currently set to "12", because I have 12 players.
The conditional formatting for the tier designation is actually based off the number in the "Color -> normal" column to the right of the table. It shouldn't need to be changed even with fewer teams, you just need to delete the cells you don't need. With additional teams, you will need to expand this formatting and formula down until all players are accounted for.
TIER DETERMINATION:
Simply put, tier determination is calculated assuming that a lower ranked player is within 95.4% of the player above.
So I actually wanted to use Gaussian Mixture Clustering for this, however, that is very difficult to implement in excel (even more so in google docs) and there was no chance this was going to be linked to MATLAB or any other similar language to allow that, so I found another way.
I thought to myself "well, if my target is to pass the person above me and I'm not that far away from them, I'd put myself in the same tier as them." I started thinking of standard deviations and uncertainty and after a little internal deliberation, I came to the conclusion that if your power score is within 2sigma accuracy of the score above you, you are close enough to be within their tier. This is how the tier breaks (brk) are determined. Yes, the ~95.4% remains a constant cutoff.
If anyone has an alternatively simple solution, I'm all ears!
COLLABORATE AND CONTRIBUTE
If you have any ideas to make this sheet better or want to build upon the work I've done, hit me up! I'm happy to collaborate, just shoot me an email via the ABOUT section above, or hit up /u/_macon on reddit.
Lastly, if you've made it this far and like what you've read, please consider buying me a beer. As you might expect, this kind of thing can take a lot of time to put together - but don't feel obligated if you don't want to. I’m just glad you find this useful.