Welcome to the second 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 Part 1 of this series, where we downloaded hitter and pitcher projections and started to set up the rankings Excel file, you can find it here.
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 2
In this second part of the series, we’ll create a new tab in our Excel file to document our league’s scoring settings. We’ll use Excel’s “Name” feature so we can calculate projected points for all players and easily make changes to the scoring system in the future.
Excel Functions and Formulas In This Post
Below are the Excel functions and formulas used in this part of the series. If you’re already familiar with what these are, you can skip ahead.
Named Cells (or Named Ranges)
Excel has a feature called “Names”, where you can give a cell, a group of cells, or an entire table a name. After giving a cell a name, you can then use the name in calculations.
In this part of the series we’ll give names to the point values of each scoring category for our league. Let’s assume each HR for a hitter is worth 9 points.
We can name this point value as “H_PTS_HR”.
If we’re later trying to determine how many points Javier Baez will earn from home runs, we can multiply Baez’s projected home runs by “H_PTS_HR” instead of multiplying by 9 or by cell B7 (in the image above).
If you’re familiar with the basic concepts of computer programming, setting the point value of home runs equal to a name is like using a variable.
The benefit of this is that we can later change the 9 to a different value. This is especially useful if we play in multiple leagues or if the scoring format changes.
It becomes very easy to change the scoring system without having to search within complex formulas. And we also don’t need to flip back and forth and remember that the HR point value was in B7 (as opposed to B6 or B8).
Using a name also gives the formula “meaning”. It’s a lot easier to understand this:
= B2 * H_PTS_HR
than it is to understand this:
= B2 * B7
Another helpful benefit is that Excel makes all of your Names available through a type-ahead feature. So if you know you have a series of Names that start with “H_PTS_”, you can see all of them by typing out part of the name. This works no matter what tab of the Excel file you are on, meaning you don’t have to flip back and forth to figure out the exact cell holding the point value.
Using Cell Shading To Indicate Meaning
As we continue to build a spreadsheet that will eventually be quite complex, it can be helpful to indicate which cells are “input” cells and which ones are “calculated” (formula) cells. An easy way to denote this is by using cell shading (or coloring).
An “input” cell would be facts or assumptions that will later be used in formulas for calculating projected points and player rankings. For example, the number of points for each home run hit or the number of teams in the league.
A “calculated” cell will obviously be some kind of formula we enter in Excel.
The color coding will help remind you what cells you can change in the future (inputs) and what cells you should leave alone (formulas, calculations).
You can use your own color scheme, but for the rest of the series I will shade cells blue if they are “input” cells and a light red if they are “calculated” cells.
Not to mention that color coding your spreadsheets also offers a nice intimidation factor to your opponents at the draft table. Who knows, someone might just think twice about getting in a bidding war with you if you’ve got a kick ass spreadsheet on-screen.
Adjust For Your League’s Scoring Settings
The example league I’m using to create these rankings for is loosely based on a scoring system suggested for Fangraphs Ottoneu leagues. It is likely very different than the scoring system in your league. Accordingly, you will need to use judgment and tailor the steps below to fit your league’s specific scoring system.