9 Responses

  1. Simon McPherson
    Simon McPherson at |

    This is amazing. Really cool.

  2. Will
    Will at |

    Hey Tanner – any thoughts on how to tweak this for leagues that have benches (deep ones at that)? I believe that this method will simply calculate a running total of stats for all players drafted by a team. Obviously that works great for a league with no benches, because all the stats are for starters, but in a league with benches I want to calculated projected standings based on the starters only. I’d imagine I’d have to keep track of everyone’s rosters and determine if the player is a starter or bench player for that team, and then calculate standings for only starters’ stats.

    1. Tanner
      Tanner at |

      Hi Will,

      When you set up the drop down menu to list out teams in your league, I think you’d have to set up a “bench” team for each. For example, “Team A – Bench”, “Team B – Bench”. Then as bench players get drafted, you would put them on the bench team and not the “regular” team.

      You could then filter the bench teams out of the pivot table (there’s a way to add a filter to a pivot table so not all items are pulled into it) OR you could just not include the bench teams in the ranking formulas you create.

      Hope that helps.

      Tanner

  3. Ben S
    Ben S at |

    Hi Tanner, Just wondering if you could walk thru the pitching side? I am having issues with ratios. Tnx!

    1. Tanner Bell
      Tanner Bell at |

      Hey Ben, that’s a good idea. I should tackle that sometime soon. But in the meantime, if I had to guess about the biggest challenge in doing this for pitching, it would be the fact that the ratios are in inverse rank order, meaning the lowest ERA and WHIP get the most rotisserie points.

      You can do this pretty easily by changing the last argument in the RANK formula.

      Using the example above, if you wanted to flip the point value for this formula: =RANK(D4,D$4:D$15,1)

      You would simply change the last “1” to a “0”, or =RANK(D4,D$4:D$15,1)

      The other challenge may simply be calculating the ratios. Similar to how AB and H were pulled, in the example above, to calculate AVG, you’d need to pull the totals for IP, H, BB, and ER to calculate WHIP and ERA.

      Let me know if this doesn’t help you and you have a more specific question after trying these things out.

  4. Ben S
    Ben S at |

    OK, thanks Tanner. I get the idea now, just wasn’t sure about the formula. Thanks again!

  5. Mike Scott
    Mike Scott at |

    I am looking to track projected standings in the middle if the season. Any tips for capturing actual stats and then adding remainder of year projections to determine this? Tough factors: injuries, trades, projection adjustments, and how much a team uses its bench. Thanks.

    1. Tanner Bell
      Tanner Bell at |

      Hi Mike,

      I do need to get around to writing this. It’s not something I’ve fully tackled yet. I can try to speak at a very high level about what I’d do.

      1. Hopefully you have a league provider that allows the “public” to see your league standings. Not all of them do this. ESPN, for example, doesn’t by default (I don’t think), but there is a setting where the commissioner can make your league visible to the public. This is helpful because you could then use Excel or Google Sheets to import your live standings data.

      2. You’d need to regularly download and import (copy & paste) in “Rest of Season” (ROS) projections. Fangraphs provides several different ROS projections .

      3. Once you have this data in a file, you could then use formulas to add each teams projected player stats to the stats they have already accumulated.

      Like you mention, bench players are an interesting wrinkle in this. I think you’ll have to manually maintain who each teams starters are in your file. Or if your league is public, you may be able to download each team’s roster. But that’d be a lot of work to set up.

      This isn’t elegant. But a perfect solution would be very hard to fully automate.

      Thanks,

      Tanner

Comments are closed.