Welcome to the first part of a series in which we’ll go step-by-step through the process of using Microsoft Excel to calculate your own rankings for a fantasy baseball points league (as opposed to rotisserie or head-to-head rotisserie).
Whether you’re in a standard points league at a major site like ESPN or a more advanced Ottoneu league at Fangraphs, this process will help you develop customized rankings for your league. These instructions can be used for a season-long points league or a weekly head-to-head points league.
If you’re looking for info on how to rank players for a roto league, look here.
I recommend going through all the parts of the series in order. If you missed an earlier part of this series, you can find it here:
Please note that this series has been adapted into a nine-part book that also shows you how to convert points over replacement into dollar values and how to calculate in-draft inflation. Click here if you’re interested in reading more about the conversion to dollar values.
ABOUT THESE INSTRUCTIONS
- The projections used in this series are the Steamer 2015 preseason projections from Fangraphs. If you see projections that you disagree with or that appear unusual, it’s likely because I began writing this series in December 2014, still early in the off-season.
- For optimal results, you will want to be on Excel 2007 or higher. Some of the features used were not in existence in older versions.
- I use Excel 2013 for the screenshots included in the instructions. There may be some subtle differences between Excel 2007, 2010, and 2013.
- I can’t guarantee that all of formulas used in this series will work in Excel for Mac computers. I apologize for this. I don’t understand why Excel operates differently and has different features on different platforms.
IN PART 6
In this part of the series we will discuss the concept of replacement level, prove that it can lead to better decision making, demonstrate how it is an objective measure for making positional scarcity adjustments, and then incorporate replacement level adjustments for each position into our projected point values.
Accounting For Replacement Level
Heading in to the 2015 season, Ryan Braun is projected by Steamer to produce 82 R, 25 HR, 82 RBI, and 13 SB (or 752 points in my example league). Buster Posey is projected for 69 R, 19 HR, 75 RBI, and 1 SB (681 points).
Braun’s raw production is clearly superior to that of Posey. But is that all we need to look at to conclude which player is more valuable? Don’t we need to include some measure of “replacement level” in this calculation? Isn’t that what WAR is all about? Wins Above Replacement?
How do I account for the fact that the day after our fantasy draft I can go out to the free agent listing and pick up an OF that would produce 61 R, 10 HR, 47 RBI, and 15 SB (478 points), or a Catcher that would produce 38 R, 9 HR, 45 RBI, and 7 SB (319 points)?
Clearly the replacement catcher is much less productive than the replacement level OF.
Using Points League Settings
You’ve been following me through the creation of a rankings file for an example league. We just finished converting projected statistics into point values for this league, so let’s take a look at comparing Braun to Alejandro De Aza (a hypothetical replacement level OF) and Posey to Christian Bethancourt (a hypothetical replacement level catcher).
|Alejandro De Aza||478|
Braun is projected for 274 points over the replacement level outfielder and Posey is projected for 362 points more than the replacement level catcher!
That means Posey is roughly 88 points more valuable than Braun, despite having lower overall projected points.
If you’re having a hard time digesting that, think of it this way. Let’s assume Braun and Posey represent second round draft picks (just go with it, don’t argue) and De Aza and Bethancourt will be last round draft picks (replacement level).
The team that takes Braun in the second round and Bethancourt in the last round would be projected for 1,071 points. The team that takes Posey in the second round and De Aza in the last round would be projected for 1,159 points. Again, that’s 88 more points than the Braun/Bethancourt combination!
This is why considering replacement level matters.
Positional Scarcity Adjustments
You have probably come across suggestions or you might have even thought to yourself that you should “bump” a player up your rankings because he plays a weak position. But is this really appropriate? How much do you bump him up?
Another great benefit of incorporating replacement level into your rankings is that it makes your positional scarcity adjustments for you!
You just saw how we proved Posey’s 681 points as a catcher are more valuable than Braun’s 752 from the outfield. Rather than arbitrarily “bumping” Posey in the rankings, we can figure out exactly where he should be ranked by calculating his “Points Above Replacement”.
Let’s look at the top 15 projected hitters in my example points league.
Not a catcher to be found. But if we presume this league has 24 starting catchers (you need to read this if you play in a two-catcher league), things change significantly when we calculate points above replacement.
Three catchers rocket into the top 10 while OF and 1B are devalued some. This movement that takes place after you calculate Points Over Replacement Level IS THE POSITIONAL SCARCITY ADJUSTMENT. Players move exactly the proper amount. No guesswork.
EXCEL FUNCTIONS AND FORMULAS IN THIS POST
Nothing really new here. We’ll just be using things we’ve already used in earlier parts of the series. We will use another VLOOKUP formula, create a table, and use structured references to build some formulas.
|1.||On the “Scoring Settings” sheet, begin to fill out the information you see in columns G and H below.|
|2.||Click and drag with your mouse to select the area of the table you have started (ignore the “Replacement Level” heading).On the “Home” tab of the Ribbon, select the “Format as Table” drop down and choose a color scheme.Make sure to specify that your table has headers (check the box). And hit OK.|
|3.||Just like with all our other tables, we should give this a name. On the “Formulas” tab of the Ribbon, click on “Name Manager”.When Name Manager appears, choose to Filter the list by “Table Names”.Locate the only unnamed table (mine is “Table4” in the example below). Click on the table in the list and then hit the “Edit…” button.Change the name of this to “REPLACEMENT_LEVEL” and hit “OK” to save the name. Then hit “Close” to exit the “Name Manager”.
|4.||Return to the “Hitter Ranks” tab. Use the drop down arrow on the “PROJ PTS” column to ensure it is sorted in descending order (players with most projected points at the top). Now click on the downward pointing arrow on the “POS” column. Click/toggle the “Select All” box until all selections are cleared (none are checked). Then click to select only the “C” position. Click “OK” to accept this filter.|
|5.||Consider your league size and roster rules and developan 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.
To determine who the replacement level catcher will be, click once on the “PROJ PTS” value of the first catcher (Posey in my example).
Then click and drag to select more players (you can see an image of this below, make sure you are highlighting only one column).
As you continue to select more players, watch the “Count:” field on the Excel status bar.
|6.||Stop highlighting when the count of catchers reaches the number you expectto be drafted (e.g. 24).
The replacement level player is the very next player that falls outside of the top 24.In the screenshot below you can see that the first catcher not to be drafted will be Rene Rivera. His projected points are 284.
|7.||Return to the “Scoring Settings” sheet. And type in the projected points for the replacement level catcher into our “REPLACEMENT_LEVEL” table created earlier (e.g. 284 for Rene Rivera).|
|8.||Repeat steps 4 – 7 for eachposition.
If your league has Corner Infield and Middle Infield roster spots, determining replacement level for the infield positions involves some additional work.
The easiest method (it’s a little imprecise) would be to assume 18 1B will be drafted and 18 3B will be drafted (12 1B to play the 1B slot, 6 1B to play CI, 12 3B to play 3B slot, and 6 3B to play CI).If you want to be more precise, set your filters to show both 1B and 3B at the same time.
Then click and drag so you can see the count of the 36 top combined 1B and 3B.
Then manually count to verify there are at least 12 1B and 12 3B (to fill the actual 1B and 3B positions).
As long as you have at least 12 1B and 12 3B, nothing else is necessary. If you don’t have at least 12 3B, take one 1B out of your group and add in the next best 3B.
For example, when I ran through my hypothetical league I found that the top 36 corner infielders were made up of 22 1B and 14 3B.
The very next first basemen and the very next third basemen outside of that top 36 are your replacement level players.
You must follow a similar process to account for the UTIL/DH spot in your roster. This will push replacement level even further down the list of players.
The UTIL/DH slot is likely going to end up changing who the last 1B and OF are. Instead of drafting the top 22 1B (in my example), my league will also likely draft even more 1B to put at the UTIL/DH spot.
NOTE: Explaining how to identify replacement level in writing is very difficult. It’s probably even harder for you to understand my scattered thoughts.
That’s why I created this video to show a practical approach you can use. The video is using “Standings Gain Points” instead of “Projected Points”, but the exact same approach can be followed. If you do follow the approach in the video you’ll have a very easy to understand color-coded result letting you know exactly who the replacement level players are.
In the end, you must verify that your projected pool of draftable players satisfies your league’s roster requirements. If you are in a 12-team league with traditional roster requirements (where each team drafts 14 hitters), select the top 168 players and verify that at least 24 C, 36 CI, 36 MI, and 60 OF are included.
Provided these limits are met, the replacement level points for each position is very next 1B, 2B, SS, 3B, or OF that fall outside of your pool of draftable players.
REPLACEMENT LEVEL FOR THE UTIL SPOT: Your UTIL slot is likely to be filled by a variety of positions (1B, 2B, OF, etc.). Because it can be filled by a player of any position, I believe you should make the replacement level points for DH/UTIL equal to the position with the highest replacement level (this is covered in the video too).
|9.||Follow steps 4-7 to determine replacement level for pitchers. This should be much simpler because even if your league uses specific “SP” and “RP” spots, you don’t need to determine replacement level for these differently unless your league has a very unique configuration that forces you into drafting relief pitchers to fill spots.
After you have identified all your replacement level players, make sure you’ve filled out the entire REPLACEMENT_LEVEL table.
|10.||Let’s focus just on hitters for a few steps.
We now need to adjust each player’s projected points to reflect their points above replacement level (like we did above in our Braun vs. Posey example).
Go to your “HITTER RANKS” tab. Make sure you have cleared any position filters. To do this, click once on a piece of data in the “HITTER RANKS” tab. Then go to the “Data” tab and click the “Clear” button.
Now add a new column header next to your “PROJ PTS” column. This is where we will pull in the Replacement Level points for each player.
Column W is the first unused column in my example file. So click in cell W1, type “REPL LEVEL”, and hit Enter.
|11.||To populate this “REPL LEVEL” columnwe want Excel to look at each player’sposition, go find thatposition in the replacement level table we just set up on the “Scoring Settings” tab, and bring back the replacement level point value for that position.
We can do this using a VLOOKUP formula.
Recall that the VLOOKUP formula is used to search in another table for a specific value (look in the replacement level information and find a specific position).
The formula to use for the VLOOKUP in cell W2 is:
You might be wondering why I typed a “2” in for the Col_index_num.
The reason is because the REPLACEMENT_LEVEL table does not start in Column A, like all our other tables have to this point. The formula “COLUMN(REPLACEMENT_LEVEL[POINTS])” returns an 8 because it’s in column H of my “Scoring Settings” tab.
Review the output of the formula. An easy way to see if everything is working properly is to look for a catcher in the list and verify the point value is consistent with your results from step 7 above.
|12.||Now let’s add a column to calculate points above replacement level for each player. My first empty column in my example file is column “X”. So in cell X1, I’ll type “POINTS OVER REPL” as the column header and hit Enter.The formula here will simply be:
Type the equals sign and then use your mouse to click on the “PROJ PTS” and “REPL LEVEL” columns as needed while building the formula.
|13.||As we looked at in the introduction to this part, it’s possible that player rankings (especially for catchers) can shift after you take replacement level into account. Sort the “POINTS OVER REPL” column by clicking on the downward pointing triangle over the column. Then select “Sort Largest to Smallest”.|
|14.||The POINTS OVERREPL column becomes very important if we are to eventually calculate dollar values for players. It’s important that this column show 0 for the replacement level players in your league.
YOU NEED TO CHECK THIS.
Recall that my example spreadsheet is being built for a 12-team league where each team drafts 14 hitters. That would be 168 hitters drafted in total.
You can see in my example below that as I approach the 168th player and move into the replacement level players the “POINTS OVER REPL” approach zero. You should see this same thing! As you approach the last few drafted players the “POINTS OVER REPL” should approach zero.
If you don’t see this type of result, you need to reevaluate replacement level and make adjustments to the “REPLACEMENT LEVEL” table on the “Scoring Settings” tab.
|15.||Repeat steps 10 – 14 for pitchers.In a 12-team league that rosters 9 pitchers, 108 starting pitcherswill be drafted, leaving the 109th pitcher as “replacement level”.
Adjust this for your own league’s settings.Even if your league has different spots for Starting Pitchers and Relief Pitchers, I do not recommend determining replacement level for RP and SP.
Make sure you perform the same check of your pitchers. As you approach replacement level, the “POINTS OVER REPL” should move towards 0. As you move below replacement level, you should see the points go negative.
|16.||Save your file. You have now completed your points league hitter and pitcher rankings!|
We have calculated the points over replacement level for each player. You no longer have to make arbitrary decisions because you’re trying to account for “positional scarcity”. The points over replacement level factors this in. It’s not as simply as comparing the points over replacement level for a catcher and comparing them to an outfielder.
CONVERSION TO DOLLAR VALUES
I have taken the six-part series you just completed and adapted into a nine-part book that also shows you how to convert points over replacement into dollar values and how to calculate in-draft inflation. Click here if you’re interested in reading more about the conversion to dollar values.
DO YOU HAVE ANY QUESTIONS?
If you have questions, it would be great if you can ask them in the comments below so others can benefit from the discussion.
If you’d like to know when I put out the next post in the series or similar posts in the future, click below to follow me on Twitter.