Welcome to the sixth and final part of the “Create Your Own Fantasy Baseball Rankings” series. If you missed an earlier part, you can find it here. You can start at the beginning of the series or if you want to start here at Part 6, you can download the Excel file created during part 5 here.
Please note that this six part series has been adapted into a 10 part book that also shows you how to convert standings gain points into dollar values and how to calculate in-draft inflation.
A few notes about the series:
- It was originally written before the 2013 MLB season. The screenshots and player references you see might refer to things from that time frame, but the same approach will work today.
- If you register as SFBB Insider, you can receive all six parts in a free, tidy, and easy-to-use e-book
- Familiarity with Excel is recommended, but I do my best to explain all formulas and functions used
- Some of the formulas used in the series do not work in Excel for Mac computers. I apologize for this. I don’t understand why Excel isn’t built to operate the same on that platform.
In this sixth part of the series we will discuss the concept of replacement level players and calculating for position scarcity.
Replacement Level Players
Mike Trout is projected for 114 R, 26 HR, and 83 RBI. Those numbers are gaudy. But should he get “credit” for all those statistics if I can go out the day after the draft and pickup a player on the free agent list that is projected for 50 R, 15 HR, and 55 RBI?
This is the concept of replacement level. If player X is projected for 26 HR and there are several free agents that will hit 15 HR, the true value of player X is in his 11 additional HR (26 – 15).
So when calculating a player’s SGP, you should not perform the calculation on the “gross” or total number of HRs. Rather, you should calculate SGP with the amount of HR over a replacement level player (a free agent).
Determining Replacement Level
Assuming a 12-team league with 14 hitters (two C, 1B, 2B, SS, MI, 3B, CI, five OF, DH), 168 offensive players will be drafted (you can add more to adjust for bench players). So the 169th player is “replacement level”, right?
Arguably this is true. But let’s fine tune this a little more. In a 12-team league where each team must start two catchers, the 25th best catcher is the “replacement level catcher”.
If the league request one 2B, one SS, and one Middle Infielder, then 36 combined 2B and SS will be drafted. We can assume this will be comprised of 18 2B and 18 SS, and the 19th best of each position will be the “replacement level”.
Likewise, you might expect 18 1B, 18 3B, and 60 OFs to be drafted. But given that these positions typically produce better offensive statistics than 2B and SS, 1B and OF tend to be slotted into the UTIL/DH spots. This can push the 1B up to 24 selected players and the OF up to 66 selected players (with the 25th 1B being “replacement level” and the 67th OF being “replacement level”.
Let’s look at some projected statistics for Jason Heyward and Robinson Cano (please note the tables below don’t foot due to rounding) :
On the surface, the two are near equals, with Heyward holding a slight overall edge in SGP. But let’s now compare each to a replacement level player at their position. In my rankings spreadsheet, the 61st ranked OF is Ryan Ludwick.
|Category||Heyward||Ludwick||Heyward over Ludwick|
In my rankings spreadsheet, the 19th best 2B is Gordon Beckham.
|Category||Cano||Beckham||Cano over Beckham|
So despite a higher gross SGP (11.54 vs. 11.25), Heyward comes out as less valuable when we adjust for replacement level players. In fact, Cano moves to nearly one whole SGP of an advantage over Heyward (5.54 vs. 4.61).
As you can see in this example, ADJUSTING GROSS SGP FOR A PLAYER BY BACKING OUT THE VALUE OF A REPLACEMENT LEVEL PLAYER AT THE SAME POSITION ADJUSTS THE RANKINGS FOR POSITION SCARCITY.
- We first need to add a new tab to our spreadsheet. Right-click on the “RANKINGS=>” tab and choose the option to “Insert…”. Then choose to add a new worksheet (Or a faster way to do this is to click once on the “RANKINGS=>” tab. Press and hold the SHIFT key. Then push and release your F11 key.).
- Right-click on the new sheet and select “Rename”. Name the sheet “Replacement Level”.
- Begin to fill out the framework of a table by keying in the following:
- Click and drag with your mouse to select the area of the table you have started:
- On the “Home” tab, select the option to “Format as Table” and choose your desired color scheme. Specify that your table has headers when prompted.
- Click on the “Formulas” tab and select “Name Manager”.
- In the ensuing menu, select the unnamed table (which should relate to the Replacement Level tab we just created). Chose the “Edit…” option and provide a new name for the table.
- Give the table a name (e.g. “ReplacementLevel_H”). Hit OK to accept the changes.
- Return to the “Hitter Ranks” tab. Use the drop down arrow on the “TTLSGP” column to ensure it is sorted in descending order (players with most SGP at the top).
- Click on the downward pointing arrow on the “POS” column. Clear all the position check boxes and select only “C”. Click “OK” to accept this filter.
Use your league settings to develop an approximation of how many catchers will likely be drafted. For example, in a 12-team league in which each team starts two catchers, there likely won’t be any catchers starting at the DH/UTIL spot. This means that 24 catchers will be drafted and the 25th catcher represents replacement level.
I believe that identifying one specific player as “replacement level” can be misleading and that it is best to develop a “composite” of the replacement level player. For example, what if the 25th catcher happens to steal a lot of bases and all his value comes from this stat. It would be misleading to compare all the other catchers to this player.
To calculate this approximate replacement player I suggest creating an average of five players. The average will be of the last draftable player and the two players above him and the two players below him. Sticking with our example that 24 catchers will be drafted, I will use catchers #22, #23, #24, #25, and #26.
Any easy way to figure out who these catchers are is to choose a specific column, click on the value for the first player in the column, and then click and drag to select more players (you can see an image of this to the right, make sure you are highlighting only one column).
As you continue to select more players, watch the “Count:” field on the Excel status bar.
In looking at the 22nd – 26th best catchers, I see a rough average of 1.39 SGPs in R, 0.87 in HR, 1.41 in RBI, 0.13 in SB, and -0.35 in AVG.
Note that if you select the RSGP information for catchers #22-#26 as shown above, Excel will display the average on the status bar in the lower right hand corner of the program.
- Enter these averages into the Catcher row on the “Replacement Level” tab.
Repeat steps 10 – 13 for each position.
If your league starts Corner Infielders and Middle Infielders, you can likely assume that will break down into an even split between 1B & 3B and 2B & SS. For example, in a 12-team league with a CI position, a total of 36 1B or 3B will be drafted (12 1B, 12 3B, and 12 CI). I usually assume this will break down to18 1B and 18 3B. This won’t be exact, but splitting hairs about this will not result in much of a benefit to your rankings.
You must perform a similar adjustment for the DH/UTIL spot in your league. Again, assuming a 12-team league, we must now adjust replacement level by 12 more players. I generally assume this will result in 6 more OF being drafted and 6 more 1B, because 1B and OF are generally the most productive positions. This means that I assume 24 1B will be drafted (12 to play 1B, 6 to play CI, and 6 to play DH/UTIL) and 66 OF (60 to play OF (5 * 12-teams), 6 to play DH/UTIL).
It is important to keep in mind that these numbers are highly dependent upon two things that will cause the numbers I present below to differ from what you will see as you work with your own spreadsheet:
- The underlying projections used
- Your league settings
The projections you use will surely be different from those I used to create this guide. For one, I created this guide using the 2013 Steamer projections. You will be using some projection set you obtained after 2013. The projections you use determine the statistics of the replacement level player. So your replacement level player will not be the same as mine.
Further, your league settings may be different. I am assuming a 12-team league starting 2 catchers. If you have a 10-team league or if your league starts only one catcher, your definition of who the replacement level player is will be different.
With those caveats in mind, here are the approximations I calculated using the 2013 Steamer projections for replacement level players assuming a 12-team league starting 14 hitters, 9 pitchers, and no bench spots:
Note, the player pool for “DH” only hitters, like David Ortiz, is very small. Too small to get a meaningful determination about replacement level. I make the assumption that 1B is the best approximation of replacement level for players only eligible at DH .
- Now that we’ve determined what “replacement level” is, we need to adjust our SGP calculations to back out the statistics of a replacement level player. The trick here is that the Hitter Ranks sheet has players of all positions and the Replacement Level sheet has a table containing the corresponding replacement level statistics for each position. To subtract out the replacement level statistics for each position, we need to adjust this formula to go into the “Replacement Level” tab, find the player’s position, then retrieve the amount to subtract. Recall the VLOOKUP formula can be used to search in another table for a specific value (look in the replacement level information and find a specific position).You’ll remember the original formula for RSGP was =[@R]/24.6
- Subtracting replacement level data from this using VLOOKUP yields this formula:
If you’d like clarification on using the VLOOKUP formula, be sure to check out Part 3 of this series.
- Add VLOOKUP formulas for the remaining hitting categories
CATEGORY FORMULA R =[@R]/24.6-VLOOKUP([@POS], ReplacementLevel_H, COLUMN(ReplacementLevel_H[R]), FALSE) HR =[@HR]/10.4-VLOOKUP([@POS], ReplacementLevel_H, COLUMN(ReplacementLevel_H[HR]), FALSE) RBI =[@RBI]/24.6-VLOOKUP([@POS], ReplacementLevel_H, COLUMN(ReplacementLevel_H[RBI]), FALSE) SB =[@SB]/9.4-VLOOKUP([@POS], ReplacementLevel_H, COLUMN(ReplacementLevel_H[SB]), FALSE) AVG =(([@H]+1768)/([@AB]+6617)-0.267)/0.0024 – VLOOKUP([@POS], ReplacementLevel_H, COLUMN(ReplacementLevel_H[AVG]), FALSE)
- Perform steps 3-8 in order to create a pitchers replacement level table on the Replacement Level tab. This will be a much simpler exercise as there is only one position for pitchers. Name the table ReplacementLevel_P.
- Perform the equivalent of step 11-13 for pitchers. In a 12-team league that rosters 9 pitchers, 108 starting pitchers will be drafted, leaving the 108th best pitcher as “replacement level”. Looking at pitchers 106 – 110, here are the replacement level average SGPs I come up with for pitchers:
POS W SV SO ERA WHIP TTL P 3.23 0 2.68 -0.85 -0.88 4.18
- Enter the averages into your newly created ReplacementLevel_P table.
- To subtract out the replacement level statistics for pitchers, we need to adjust the formulas to go into the “Replacement Level” tab, find the pitcher replacement level statistics, then retrieve the amount to subtract. Recall the VLOOKUP formula can be used to search in another table for a specific value (look in the replacement level information and find a specific position. You’ll remember the original formula for WSGP was =[@W]/3.03.Subtracting replacement level data from this using VLOOKUP yields this formula:
=[@W]/3.03-VLOOKUP([@POS],ReplacementLevel_P, COLUMN(ReplacementLevel_P[W]), FALSE)
- Add VLOOKUP formulas for the remaining pitching categories. Note, I assume all starting closers are taken in 12-team leagues; therefore, there is no reason to make a replacement level adjustment.
CATEGORY FORMULA W =[@W]/3.03-VLOOKUP([@POS], ReplacementLevel_P, COLUMN(ReplacementLevel_P[W]), FALSE) SV =[@SV]/9.95 SO =[@SO]/39.3-VLOOKUP([@POS], ReplacementLevel_P, COLUMN(ReplacementLevel_P[SO]), FALSE) ERA =((475+[@ER])*9/(1192+[@IP])-3.59)/-0.076-VLOOKUP([@POS], ReplacementLevel_P, COLUMN(ReplacementLevel_P[ERA]), FALSE) WHIP =((1466+[@BB]+[@H])/(1192+[@IP])-1.23)/-0.015-VLOOKUP([@POS], ReplacementLevel_P, COLUMN(ReplacementLevel_P[WHIP]), FALSE)
- It’s possible that some players would rise or fall in the total SGP ranking after these changes. Sort the hitter and pitcher ranks from highest value to lowest value again using the drop down arrow over the “TTLSGP” column.
It is extremely important to perform a reasonableness check when you’re done with this exercise. If you have done everything correctly, when you’re done, you should see that the number of players with positive SGPs should be approximately equal to the number of players your league will draft.
For example, I have been assuming a 12-team league where each team drafts 14 hitters. This means 168 hitters will be drafted. If I look in my “Hitter Ranks” tab, I should see that the players right around the #168 mark will have very close to 0.00 for TTLSGP.
Row #169 represents the 168th player (accounting for the header row in the sheet). And you can see everyone here is about at 0.00 SGP
If you find that you have too many players above 0.00 TTLSGP, you must raise your replacement level calculations. If the replacement level averages are higher, fewer players will come out above 0.00. If you have too few players above 0.00 TTLSGP, you must lower your replacement level calculations. If replacement levels are lower, more players will come out above 0.00.
- You did it! You now have completed hitter and pitcher rankings based upon reliable projection data that also takes into account and adjusts for position scarcity.
You have just created a very powerful and flexible tool. You can easily adjust the projection data and have your changes instantly flow through the calculations and update the rankings accordingly.
LINK TO DOWNLOAD FILE: SFBB Rankings – Part 6.XLSX
IF YOU’VE MADE IT THIS FAR…
You might be interested to know that I’ve recently completed parts 7 through 10 of the “Create Your Own Rankings” series. These additional segments include the calculation of dollar values and incorporate calculations for keeper and in-draft inflation. All ten parts of the book are now available in Kindle format at Amazon. Please click here to read about these additions to the guide and how you can purchase all 10 parts. My hope is that this series is the best step-by-step guide to calculating SGP-based dollar values and inflation is available anywhere.