Tag: Create Your Own Rankings

Is There a Hitting / Pitching Split for a Points League?

I recently started working on my next book looking at how to create customized rankings and dollar values for a points league.  For the most part, much of the same principles followed for ranking players in a rotisserie league still apply.  But then I stumbled upon a major difference, one that I can’t find guidance on anywhere.

Points Leagues Cut Right To The Chase

One really nice thing about points leagues is that the goal is much more straightforward than in a roto league.  Instead of trying to compose a balanced team in the 10 different categories of a rotisserie league, you’re really just competing in one category… total points.

Because of this, we can cut through some of the exercises we have to do for ranking a roto league.  We don’t have to mess around with league history, determine Standings Gain Points for each category, and don’t have to convert each player’s projections into SGP.

We Just Have To Calculate Projected Points For Each Player, Right?

This is the biggest task, for sure.  Make sure to build in a replacement level calculation, too.

I think that’s what most people would do and just stop there.  But if you want an edge, I think we can dig a little deeper.

Quickly, Here’s How I Would Calculate Projected Points

I’m going to go into much more detail in the finished guide and include screenshots, tips, and tricks to make this a spreadsheet you can use into the future.  But here’s the high level:

  1. Download a set of reliable projections
  2. Document your league’s scoring settings (e.g. 10 points for a HR, 2 for SB, etc.)
  3. Multiply the projected stats by your scoring settings
  4. Determine and adjust for replacement level

Follow these steps and you’ll end up with the total number of “useful” or “draftable” points.

Why Do We Have a Rotisserie Split?

Continue reading “Is There a Hitting / Pitching Split for a Points League?”

How Do You Account For and Value Players with Multiple Position Eligibility?

Zobrist_Prado_Santana_CarpenterHow do we handle the multi-position players like Ben Zobrist and Martin Prado?  When a player is eligible to be slotted at 2B, SS, and OF, how do we value that player?

This question came up in the comments of my last post on “How to Add Positional Ranking to Your Spreadsheet“, from a reader named Michael (I welcome your questions too).  In this post I’ll take a look at how I handle this and look at a more inticate approach you could take to get the information.

I must warn you that it takes a lot of new formulas and manipulation of your existing rankings spreadsheet to accommodate multiple positions.  To make sure you have something to reference, at the end of this post I’ll provide a download link to an example Excel file you can download.

Assign the Player to the Weakest Position They’re Eligible For

This is what I currently do.  For example, let’s take a player like Ben Zobrist, who in the 2014 season currently qualifies at SS, 2B, and OF.

In the Player ID Map, I attempt to classify each player at the weakest position they’re eligible for.  I do that by determining what replacement level is for a standard 12-team mixed rotisserie league.

Is the “weakest position” going to be the same for every league?  No it’s not.  It’s probably close in most leagues.  Catcher will almost surely be the weakest in any format.  Then Shortstop will generally be the next weakest, followed by 2B, 3B, 1B, and OF.  But positions might change a little in an 8-team league or a 15-team league, in an AL-only league versus a standard league.

That’s a big reason why I started this site.  It’s not always safe to give blanket advice, and I think the best approach is to calculate all of these things for your own league.  You’ll be better off for it.

How Do I Determine the Weakest Positions?

Assuming you’ve done some kind of work to create your own rankings (if not, start here), the weakest position can easily be determined by looking at the replacement level information you’ve calculated (if you want to refresh your memory on replacement level, read this).

Looking back to one of my preseason files for the 2014 season, this is the replacement level information for one of my leagues.
REPL_LEVEL_TABLE

The weaker positions are those with the lowest replacement level.  So in this league it’s C, SS, 2B, and then 1B, 3B, and OF are essentially the same.

Back to Zobrist

Going back to our example of Ben Zobrist who is eligible at SS, 2B, and OF, if we’re trying to assign him to the weakest position he’s eligible for, he would be assigned to SS.

Thinking of Martin Prado who is eligible at 2B, 3B, and OF, he would be assigned to 2B.

Carlos Santana who is eligible at 3B and C would be assigned to C.

Why Do I Only Assign a Player to One Position?

I have two reasons for this.

The main reason is because assigning players to the weakest position they are eligible for gives the player his greatest value.  I’ll demonstrate more on this in a minute.  But if you’re calculating Zobrist’s dollar value, it comes out highest when he’s classified as a SS.

Going along with this, fantasy baseball leagues are becoming more efficient market places.  As we all get better and smarter about playing fake baseball, people generally realize they’re best off putting Buster Posey at catcher and not at 1B.

This won’t always be the case, but for the most part the obvious situations like your Buster Poseys and Carlos Santanas are going to be assigned where they belong.  Because of this, it’s somewhat of a wasted effort to try calculating values for them at 1B or 3B.

The second reason is a technological one.  You run into a lot of trouble having the same player appear multiple times in one spreadsheet (on multiple rows).  Not only does it become confusing to have to remember that Zobrist’s name appears three times in your draft list, but it also greatly complicates (or eliminates) your ability to calculate dollar values.

How Much Does a Player’s Position Affect Their Value?

Is there really a big difference between a SS and an OF?  Let’s take a look.

I am running the exercise below using Steamer’s 2014 preseason projections.  The dollar values assume a 12-team standard mixed rotisserie league with 14 hitters (C, C, 1B, 2B, 3B, SS, CI, MI, OF, OF, OF, OF, OF, UTIL) and 9 pitchers.  The dollar values are calculated using standings gain points and my approach to calculating player values.

Here’s the replacement level information again:REPL_LEVEL_TABLE

Let’s start out with Zobrist.  You can see below that he was projected for 8.73 SGP before adjusting for replacement level.  When you then account for replacement level and figure out his “SGP Over Replacement Level” you see that he becomes much more valuable as a shortstop.  Over $4 more valuable than when he’s classified as an OF!

BEN_ZOBRIST_REPL_LEVEL Continue reading “How Do You Account For and Value Players with Multiple Position Eligibility?”

An Even Better Positional Ranking Formula

In my last post about how to calculate positional rankings in your player spreadsheet (link), I realized a weakness in the approach I outlined.  I’m here to fix that with a slightly more complex formula. Final_Excel_Formula_Output

The Weakness In The First Approach

The problem with the first approach is that it is entirely dependent upon how the file is sorted.  If the file is sorted by dollar value or standings gain points in descending order, then the rankings work.

But if you sort the file by home runs, stolen bases, or player name, then the rankings fall apart.  The player with the most home runs (or the most stolen bases or the first name alphabetically) becomes the top ranked player.

The COUNTIFS Excel Function

Instead of the COUNTIF function, we’ll use the COUNTIFS function this time.

You might remember from the last post that the COUNTIF function will count the number of cells in a specific range that meet a specified condition (e.g. “Hey Excel, count all the players in this column that have a ‘POS’ of ‘OF’.”).

Well the COUNTIFS function counts the number of cells in a specific range that meet all of the conditions you provide (you can give multiple conditions).

In plain English, our goal is to have Excel count all the players in our list of hitters that have a “POS” of “OF” AND that have a higher projected SGP than our player being evaluated.

We’ll get into the specifics of our formula in a bit, but here’s a screenshot of the Excel formula wizard for our COUNTIFS function.COUNTIFS_FUNCTION

This formula allows for an open-ended number of arguments, but you do need to pieces of information for each set of criteria you want to specify.  Each criteria requires:

  1. Range – This is the block, column, or area of cells we want to count from.  “Excel, look in Column E and count the cells that meet this condition I’m about to tell you about in bullet #2.”
  2. Criteria – This is what we are evaluating the cells for.  “Count the cells in Column E that show ’1B’ as the position.”

If we want to specify three conditions that must be met, then we would need six arguments (three ranges and three criteria: range1, criteria1, range2, criteria2, range3, criteria3).

Step By Step Instructions

Continue reading “An Even Better Positional Ranking Formula”

How To Add Positional Ranking To Your Spreadsheet

I just came across an interesting use of an Excel formula that will let you easily determine what a player’s ranking is at their given position (1B-1, 1B-2, 1B-3, etc.).

Here’s an explanation that might show what I’m talking about.  You’re scrolling through your huge list of ranked hitters (see image below).  You have them sorted by Total Standings Gain Points (column V) in descending order.

You see Edwin Encarnacion’s name pop up in row #38.  You know he’s a first basemen, and you can pretty easily determine that he must be the #37th ranked player (by showing up on row 38).  But now you want to know where he ranks amongst only other first basemen.

Without_POS_Rank

In this post I’ll show you a formula we can use to get our spreadsheet to look like this (look at column W):With_POS_Rank_Edwin_Encarnacion

I’m a Moron

I got nearly to the end of this post when I started to think it was weird that Encarnacion was ranked #37, Starlin Castro #32, and Paul Goldschmidt #21. Turns out I used a rankings file from the 2013 preseason for all the screenshots…

I decided against starting over because it’s not the player names that are important, we’re mostly looking at a new formula. And I found it pretty interesting and thought provoking to look at these old lists and see names like Nori Aoki and B.J. Upton so high.

Excel Formulas Used In This Post

Using the “&” to Build Text

As you can see from the image above, we’re trying to take each player’s position (e.g. “1B”) and then add a dash and then the player’s positional ranking (e.g. “1B-8” for Encarnacion).

You can use the ampersand (would you know what that was called without “Wheel of Fortune”?), in an Excel formula to add text from different columns.

A real practical application of this is to build a player’s full name (e.g Billy Hamilton) from their first name and last names being in separate columns.  Here’s an example:

Billy_Hamilton_Jose_Altuve_Craig_Kimbrel_Madison_Bumgarner Continue reading “How To Add Positional Ranking To Your Spreadsheet”

Designing Reusable Fantasy Baseball Spreadsheets

Why do your spreadsheets have so many tabs?

When you’re building a rankings spreadsheet, why don’t you just build the calculations right on the projection tab?

Why do you even use this ridiculous Player ID map?  There’s so much useless information on that thing.

Have You Ever Wondered These Things?

I have to say that I haven’t had anyone ask these questions, but I feel the need to address the questions nonetheless.  I don’t mean for them to be overly complicated, but there’s a good chance that the spreadsheets I design can be overwhelming.  So let’s take a closer look at the reasoning behind things.

There Is More Than One Way To Skin a Cat

I don’t think it’s much of a stretch to say that spreadsheet design is a form or art.  There are many different ways to get to the same end goal.  I’m always looking for new ideas and methods to add to my Excel work, and I’m certain there are more efficient and better ways to design things I have done.

With that said, I believe the principles I’m about to talk about are universal.  Whether you like the way I’ve designed things or if you prefer to do things a different way, using these concepts should help you out in the long run.

Why Do Your Spreadsheets Have So Many Tabs?

There are two main reasons for this:

  1. I want all of my spreadsheets to be reusable.
  2. I try to set things up so you only have to enter a piece of information one time.

Reusable Spreadsheets

We’ll take a close look at reusable design in a moment, but think about the different tabs/worksheets I use.  Raw projections, player information, replacement level information, and calculations all on separate worksheets.

By setting these up on distinct areas of a spreadsheet it allows you to easily update one of those components without screwing up the whole model.  Have new projection info?  Just replace the data on the projection tab.  Have a new Player ID Map?  Just copy it over top of the existing one.

This setup makes it much easier for your work to be updated during the season and into future seasons.

Entering Information Only Once

Dollar_Value_InputsIf you’ve read my book on calculating dollar values and in-draft inflation, you’ll recall that we added a “Settings” tab to the rankings spreadsheet.  On that tab we color-coded blue cells to indicate input cells.

These are just basic settings about the league, but by separating them out onto the “Settings” tab you only have to type the information one time.  If we didn’t do this, we would have to embed this information into our calculations for both the hitting and pitching rankings.

Everything is also formula-driven, so if a 13th team is added next year, or if team salary caps increase, or if you want to try a 65% hitting allocation, you can easily change those inputs and all calculations for hitters and pitchers update automatically.

If we didn’t have things set up this way you would have to search deep inside complex formulas on the hitter tab and edit these inputs.  And then you’d have to go do the same thing on the pitcher rankings.

And if you’re in more than one league you can set your rankings up for one league, just change the few settings different between the leagues, and you’ll easily have rankings tailored to each league you play in.

Why Don’t You Just Build Your Calculations Right on the Projections Tab?

Whether you’ve downloaded projections from another website or you’ve created your own, it might seem like I’m over-complicating things by having the projections on one tab and then using formulas to pull the projections to a whole other worksheet where I then calculate rankings, SGP, and dollar values.

If I were to use this spreadsheet one time, I would agree with this line of thinking.  But take a look at this image.

Rankings_Spreadsheet_DesignThis is a very primitive flow-charting model of a rankings spreadsheet.  You have a few sets of raw data as inputs (the blue rhombuses, or is it rhombi?) – the projections, the Player ID Map, and replacement level data. Continue reading “Designing Reusable Fantasy Baseball Spreadsheets”

How To Evaluate a Trade Using Standings Gain Points

In this video I’ll show you how to add a Trade Evaluator into your existing rankings spreadsheet.

Here’s an animated image demonstrating the finished product.  This spreadsheet will pull in all the Rest of Season projections for a player, their total SGP to be earned the rest of the season, and the player’s dollar value (provided you’ve added dollar value calculations to your sheets).

TradeEvaluator

I’ll also show you a practical example and explain a few important things to think about when considering trade offers.

The video is roughly 30 minutes long, but keep in mind that just about everything you create by following the guides on this site are long-term in nature.  With a little bit of maintenance, all of these tools can be used all season long AND into future seasons.

The Step-By-Step Process

I start with a spreadsheet that has already been updated with RoS Projections.  I then show you how to add a tab just to evaluate trades and other roster decisions.  We’ll add drop down menus that pull each player’s statistics, dollar value, and SGPs.

This information will enable you to add clarity to all your roster moves.  No more using your gut to analyze a 2-for-3 player trade involving hitters and pitchers.  You’ll be able to see exactly which side of the offer is better.

If you’re new to the site, I would suggest getting familiar with How To Create Your Rankings Using Standings Gain Points.

A Quick Suggestion

WatchVideoDoubleSpeedIf you’re looking for a way speed things up by watching them 1.5 or 2 (double)  speed, cutting down the time it takes to watch significantly.  Just adjust the settings at the bottom of the video player.  Click the cog and change the “Speed to 1.5 or 2.

I also recommend watching the videos in HD.  A lot of the detail in Excel can only be seen well in 720p or higher.

And a Disclaimer

I created this video using Prince Fielder, Jose Abreu, Billy Hamilton, and Ian Kinsler in an example trade.  News that Fielder is facing season-ending surgery came out the next day!  I apologize for this glaring problem with the example, but hope you can still see the power of using a tool like this to evaluate trades and free agency acquisitions.

Here’s The Video

Thanks for Watching

Stay smart.

Questions?  Comments?  Future Video Ideas?

Let me know in the comments below.

How To Use SGP To Rank and Value Players During the Season – Part 6 – Adjust Replacement Level

Welcome to the final part of the series in which we go through the process of plugging Steamer’s Rest of Season (RoS) projections into your existing ranking/dollar value spreadsheet so you can make informed and objective roster decisions during the season.

If you register as an SFBB Insider (it’s free), you can receive the entire series in an easy-to-use e-book (also free) along with two other helpful guides.  I’ve also written a comprehensive guide on ranking players and calculating player dollar values that’s available at Amazon.

Introduction

In this sixth part of the series we will revisit the concept of replacement level and adjust replacement level for our updated RoS projections.

Reminders About Replacement Level

If you’re new to the concept of replacement level read the introduction here (don’t go into the “Step-by-Step Instructions”.  When you consider the injuries that occur in Major League Baseball, rookies being called up, players underperforming projections, and others exceeding projections, the player pool is constantly changing.

In order to make the best possible decisions and to calculate representative dollar values, it is very important that we update the estimate of replacement level.

Caution:  No Further Adjustments Necessary

When listening or reading fantasy advice, you might come across a piece of advice that goes something like this, “You really need to draft a SS early to account for the lack of depth at the position.  Go ahead and reach for that shortstop.”

Don’t listen to that advice when you’re using the approach we’re now going through.  The replacement level adjustment that follows is already calculating the effect of depth at each position.  And it does it mathematically.  There is no guesswork going on here.

DON’T MAKE ANOTHER ADJUSTMENT.

You do not need to make an arbitrary adjustment to shortstops or catchers, or any other position.  When you have added this adjustment to your rankings, each player will be ranked according the their value over the worst players at the position.  If the position is weak, that’s accounted for.  If the position is deep, it’s accounted for.

If you then decide to make arbitrary adjustments to your rankings after adding in the replacement level calculation, YOU ARE DOUBLE COUNTING.

You will be reaching for players and you will be hurting your team.  Don’t “bump” players up a list because of their position.

Step-By-Step Instructions

Continue reading “How To Use SGP To Rank and Value Players During the Season – Part 6 – Adjust Replacement Level”

How To Use SGP To Rank and Value Players During the Season – Part 5 – Update the Player ID Map

Welcome to the fifth part in a series of posts in which I’ll go through the process of plugging Steamer’s Rest of Season (RoS) projections into your existing ranking/dollar value spreadsheet so you can make informed and objective roster decisions during the season.

If you register as an SFBB Insider (it’s free), you can receive the entire series in an easy-to-use e-book (also free) along with two other helpful guides. I’ve also written a comprehensive guide on ranking players and calculating player dollar values that’s available at Amazon.

Introduction

In this fifth part of the series we discuss updating the Player ID Map to pull new players into the rankings information.

Player ID Map

The SFBB Player ID Map contains the Fangraphs, MLB, Baseball-Reference, Retrosheet, CBS, NFBC, ESPN, Baseball Prospectus, Davenport, and Yahoo player IDs for over 1,200 players.  It’s not a comprehensive list of past players by any means, but I make a concerted effort to have all current fantasy-relevant MLB players and those minor league players likely to make a fantasy impact this season.

PlayerIDMap

To give credit where credit is due, I originally downloaded the player map from Crunchtimebaseball.com and tailored it to meet my needs.  CrunchTimeBaseball is run by Tim Blaker.  He continues to maintain his own map of player IDs and generally keeps his more up-to-date than I do.  You can obtain his version here.

Why Update the Player ID Map

April inevitably brings us players that were never intended to be fantasy relevant; minor leaguers that were not anticipated to make the jump, role players thrust into starting jobs, and more.  As the summer rolls on, impact rookies begin to get called up that may not have been in the preseason Player ID Map.  When September arrives there will be more of the same.

While it is possible to add individual players to the player ID map, it can be inefficient to add more than a handful.  Updating the entire list is probably easier.

Step-By-Step Instructions

Continue reading “How To Use SGP To Rank and Value Players During the Season – Part 5 – Update the Player ID Map”

How To Use SGP To Rank and Value Players During the Season – Part 4 – Add the IFERROR Formula

Welcome to the fourth part in a series of posts in which I’ll go through the process of plugging Steamer’s Rest of Season (RoS) projections into your existing ranking/dollar value spreadsheet so you can make informed and objective roster decisions during the season.

If you register as an SFBB Insider (it’s free), you can receive the entire series in an easy-to-use e-book (also free) along with two other helpful guides. I’ve also written a comprehensive guide on ranking players and calculating player dollar values that’s available at Amazon.

Introduction

In the fourth part of the series we’ll introduce a new Excel formula to help remove lookup errors, like those shown above, from our spreadsheets.  These occur when we have instructed Excel to do a VLOOKUP to find a player’s RoS projections and Excel is unable to find the player ID within the projection data.

Often times a player will stop appearing in the RoS projections.  This might be because they’ve suffered a season-ending injury, they’ve retired, or they’re an unsigned free agent. If that player remains in the list of hitters or pitchers rankings, no projection can be found for that player.  I’ve realized that the rankings and dollar value formulas I previously used did not handle these situations very well, so it’s necessary to adjust these formulas slightly.

Adding this formula to your spreadsheet is a one-time fix.  You won’t need to go through this part when you download updated RoS projections in the future.

Excel Functions in Part 4

IFERROR

The IFFERROR function allows us to control what happens when another function being used is calculating an error.  The image below is a great example of this.  On our “Hitter Ranks” tab we have a series of VLOOKUP formulas that instruct excel to go find Kendrys Morales’ player ID (moralke01) in the “Steamer Projections” tab.  During the 2014 season Morales is likely not included in the RoS projections because he remains unsigned by any Major League team.

IFERROR-Excel-Formula

The IFFERROR function will allow us to replace the error message with any value of our choice.  It essentially works by telling Excel, “If this other formula I’m using comes back with an error, use this instead”.

The formula requires two inputs:

IFERROR(value,value_if_error)

  1. Value – This represents the formula or calculation we want Excel to perform.  In our example above it will be the same VLOOKUP formula we already have entered.
  2. Value_if_error – This represents the value or message we want Excel to return if the first argument, “Value”, returns an error.  In our example above we don’t want the default “#N/A” error message that turns up if Excel cannot locate Kendrys Morales in the RoS projections.  Instead, we could just ask for Excel to return zeroes for his projected stats.

Step-By-Step Instructions

Continue reading “How To Use SGP To Rank and Value Players During the Season – Part 4 – Add the IFERROR Formula”

How To Use SGP To Rank and Value Players During the Season – Part 3 – Delete Old Info and Insert New RoS Projections

Welcome to the third part in a series of posts in which I’ll go through the process of plugging Steamer’s Rest of Season (RoS) projections into your existing ranking/dollar value spreadsheet so you can make informed and objective roster decisions during the season.

If you register as an SFBB Insider (it’s free), you can receive the entire series in an easy-to-use e-book (also free) along with two other helpful guides. I’ve also written a comprehensive guide on ranking players and calculating player dollar values that’s available at Amazon.

Introduction

In this third part of the series we will remove the old (preseason) projections from our spreadsheet and paste in the new information.  It sounds simple, but there are a few tricks to be aware of.

Step-By-Step Instructions

Continue reading “How To Use SGP To Rank and Value Players During the Season – Part 3 – Delete Old Info and Insert New RoS Projections”