Welcome to the third part of the “How To Calculate Rankings For a Points League” 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).
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:
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 3
In this part of the series we’ll use Excel’s VLOOKUP and IFERROR formulas as well as Table and Structured Reference features to pull hitter information and projections from other areas of the spreadsheet in order to create our hitter rankings tab.
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 to the step-by-step instructions.
One of the most powerful Excel formulas, in my opinion. And it’s easier to use than you might think.
This formula searches the first column of a table for a desired value (a player ID) and then returns a value that is in the same row but in a separate column. For example, we might tell Excel to go into a table of projection data, locate the player ID for Billy Hamilton (10199), and give us back the number in the fourteenth column (column N, which holds the number of SBs).
This formula requires four inputs:
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
- Lookup_value – This is the value we want to search for in the table of data (e.g. Billy Hamilton’s player ID 10199). In the rankings spreadsheet, we’re mostly going to use player IDs for this. “Hey Excel, go look for this player ID”.
- Table_array – This has to be two or more columns of data. Excel will look for the Look_up value in the first column in the set of data. You do not necessarily need to include the first column on a spreadsheet tab (you don’t have to use Column A of a sheet, the first column of your table could be Column G). But Excel is going to look through the first column you provide. “Hey Excel, here are fifteen columns of data for you, look through everything in the first column for the Lookup_value.”
- Col_index_num – This is the column number that of the Table_array that contains your desired information. This has to be a number and it has to be within the Table_array you provided. For example, if your table_array only has five columns, but you put a six for Col_index_num, you’ll have a problem. “Hey Excel, the fourteenth column has projected stolen bases. After you find Billy Hamilton’s player ID, tell me how many stolen bases are in that fourteenth column.”
- Range_lookup – This input can be either “TRUE” or “FALSE”. If you use “TRUE”, Excel will look for an approximate match of the lookup_value (PLAYERID). If you enter “FALSE”, Excel will only look for an exact match. This is an optional input, but I feel very strongly that it must be used and that “FALSE” is the option selected. You may otherwise get the wrong projections showing up for players.
TABLES (NAMED RANGES, Structured References)
Similar to how we named individual cells in the last part of our series, Excel has functionality that allows you to convert a block of data (player projections) into a named table. There are quite a few benefits to using tables:
- Tables have names. This is great for the Table_array input in the VLOOKUP formula. We can give the projection sheet the name “STEAMER_H” (for Steamer Hitters projections) and use that instead of traditional way of selecting data in Excel (something like’Steamer Hitters’!A1:W500). Not only is this a huge time saver (using your mouse to scroll and select 20 columns and 500 rows takes a long time), but it gives your formulas meaning. When you look back at your VLOOKUP formula and see “STEAMER_H”, you’ll easily be able to remember that you’re looking up projected Steamer hitter information.
- Columns have names. I have a hard time remembering what column projected HRs are in. But I don’t need to if I know that the column name is “HR”. If you don’t use a table, you’re stuck trying to remember things like, “were HRs in column G, H, or I?”. And then you have to figure out if column I is column number 8, 9, or 10? When referring to a column, use the following convention – TABLENAME[COLUMNNAME]. The column name is surrounded in brackets.
- Formula consistency. In a table, all formulas within a column are identical. When you change the formula in one cell of a column, the rest of the column automatically updates too. No more editing a formula in one cell and having to copy it to hundreds of other cells in the same column.
- Easy sorting and filtering. As easy as clicking a drop down arrow.
This function returns the column number of a cell or range of data. The function only requires one input; the cell or range to be evaluated:
Let’s use a real example to illustrate:
This formula will look for the stolen bases column in the Steamer Hitter Projections and will return the column number. If SB are in column N, this formula calculates to 14.
The IFFERROR function allows us to control what happens when another formula being used is calculating out to an error.
The image below is a great example of this. In this spreadsheet we have a series of VLOOKUP formulas that instruct Excel to go find Kendrys Morales’ player ID (moralke01) in the “Steamer Projections” tab.
You may recall that during the 2014 season Morales remained an unsigned free agent until well into the season, so he was not included in the Steamer projections. Because he was not included, the VLOOKUP formula could not find his player ID and could only calculate to this “#N/A” error message.
The IFERROR 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”.
Using IFERROR we could instead make Kendrys Morales line look like this:
The formula requires two inputs:
- 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.
- Value_if_error – This represents the value or message we want Excel to return if the first argument, “Value”, happens to be 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.
|1.||Go to the “Steamer Hitters” tab of your Excel file.We will convert this to a “table” in Excel in order to make the data easier to work with.Click anywhere within the projection data. Then locate the “Home” tab in the Excel menu system (“the ribbon”).Click once on the “Format as Table” drop down, and then select your desired color scheme.You will then be prompted to verify the range of cells in the table and that your table has a header row (e.g. Name, AB, H, HR, etc.).
You might notice that my example projection data goes out to column AC. Yours may have more or less columns. I believe the Steamer downloads contain additional information at certain times of the year, but if you look closely at the information in the rightmost columns, they’re things we don’t care about for fantasy (wRC+, WAR calculations, etc.).
As long as you have the main offensive categories and the “playerid” column, you’re good.Check “My table has headers”. Click OK.
After accepting this, your bland looking data will transform into a nicely formatted table.
|2.||We’ll later be pulling information from this table into other worksheets. To make the pulling of data easier we need to move the Fangraphs player ID to be the first column (you can use VLOOKUP if the player ID is in the first column, otherwise you’re stuck using more difficult and/or multiple formulas). Right-click on the top of the fangraphs player ID column (I right clicked on the “AC” column header) and Cut it.|
|3.||Now right-click on the top of the player name column (column header “A”) and select “Insert Cut Cells”.|
|4.||Before we finalize this table, we should give it a name we can refer to in the future. Go to the “Formulas” tab on the Excel ribbon and click on the “Name Manager” button.|
|5.||Because we created named cells in Part 2 of the series, it might be helpful to filter the Name Manager to only show table names. Do this by clicking the “Filter” drop down menu in the top right of the window. Then choose “Table Names”.Excel will give very generic names to a table, like “Table2″. It’s more helpful to give meaningful names to your tables. As you get more tables in a spreadsheet, it can become very difficult to remember the difference between “Table2″ and “Table3″. Note how there is already a table named “PLAYERIDMAP”. I previously set up this table before you inserted it into your Excel file in Part 1of this series.Select your table from the list (it will probably be named “Table2”) and click on the “Edit…” button.|
|6.||Give the table a meaningful name. I chose “STEAMER_H” to indicate these are the Steamer Hitter projections. Click “OK” to close the “Edit Name” menu. Then click “Close” to exit the “Name Manager”.|
|7.||We now have two tables ready to pull hitter information from, PLAYERIDMAP and STEAMER_H.I’m going to pull from these two tables into a new tab where I’ll calculate the hitter rankings.If you’re wondering why I’m going through all of this trouble to set up multiple tabs and to keep all of this information separated, it’s because I want this whole spreadsheet to be usable in future seasons.
By keeping the PLAYERIDMAP in one area, projections in another, and rankings in another, we’ll be able to easily update each of those components at later times.
This spreadsheet can be used in the middle of the season to drop in Rest of Season Projections. It can easily be tweaked a little and used next year.
You can read more about my design philosophy here.
|8.||Right click on the “PLAYERIDMAP” tab and select the option to “Insert…”. Choose the “Worksheet” option and click “OK”.|
|9.||Right click on the new sheet tab and choose to “Rename”. Call this sheet “Hitter Ranks”.After you’ve renamed the sheet, type “PLAYERID” into cell A1. This will be a column header for our next step.|
|10.||As we talked about in the first part of this series, I like to use Baseball-Reference player IDs as the main ID system in my spreadsheets. That’s because I can usually look at an ID and know who the player is (e.g. encared01 is Edwin Encarnacion).So we want the first column of this new sheet to contain the Baseball Reference player ID and we only want hitters at this time. The PLAYERIDMAP sheet has a list of all fantasy-relevant players (for standard rotisserie leagues, at least). However, this sheet contains pitchers also. Because this sheet is an Excel table, it’s easy to filter out those pitchers.
Click on the drop down arrow on the “POS” column. You’ll be presented with a list of all the positions. Uncheck the“P” value (for pitchers). Click OK to apply the filter.
The Player ID Map will now list only hitters.
|11.||Starting at the first player and ending with the last, click and drag within the “IDPLAYER” column (first column) and select all players (or select the first player and then use the keyboard shortcut SHIFT + CTRL + Down Arrow). Only select from this first column.Note, I also added a dummy player name “Last Player”. You don’t need to grab him, but it won’t hurt if you do.|
|12.||Copy this selected data. Return to the “Hitter Ranks” sheet. Then paste the data into cell A2.|
|13.||Now that we’re starting this new sheet, we should convert it to an Excel table. We will essentially repeat step 1 above, but for this new worksheet.
Click once to select any player ID. Then locate the “Home” tab in the ribbon).
Click once on the “Format as Table” drop down and then select your desired color scheme.
|14.||You will then be prompted to verify the range of cells in the table and that your table has a header row. Check “My table has headers”. Click OK.|
|15.||Repeat steps 4-6 in order to give the table a more meaningful name. In my example, Excel defaulted the table name to “Table3″. I renamed mine to be “MYRANKS_H” (to indicate hitter ranks, because we’ll also be ranking pitchers).|
|16.||Now for the fun part… pulling data from the other tabs. I’ll first add “Player Last Name” to the table.To expand your table, simply type into column B and hit Enter (I typed into cell B1). Excel should automatically pull this new column into your table. I’m going to name my column “LNAME”.
|17.||Your table shading may look like this instead of the image from step 16:If so, click once in the area between the Column “A” header and the Row “1” header (the top left corner of all cells), to select all cells in the entire sheet.Then click the “Fill Color” icon (looks like a paint can) drop down arrow and choose the “No Fill” option.You should now see the proper alternating color scheme.|
|18.||Our goal in Column B is to instruct Excel to take the PLAYERID from column A (e.g. “abreujo01”), go into the first column of PLAYERIDMAP, find the matching PLAYERID, and then pull back the corresponding LASTNAME. You can see in the image of the PLAYERIDMAP below that LASTNAME is in the fifth column (column E).
To start building the formula, click in cell B2 of your Hitter Ranks tab. Then click the “fx” button of the formula bar to launch Excel’s formula wizard. We’ll use the wizard the first time through to better explain this function in Excel.
|19.||Type “vlookup” into the search bar and click “Go”. When the function appears, make sure it is selected and hit “OK”. You should then see the different arguments, or components, needed for the formula to work.|
|20.||The “Lookup_value” is the value on the “Hitter Ranks” sheet that we want to locate (or match to) in the “PLAYERIDMAP”.
Click once in the “Lookup_value” field. Then click on the value in cell A2 (“abreubo01″).
Excel will convert this to [@PLAYERID]. This naming convention is referred to as a “Structured Reference” or a “Table Reference” and can be used when you have converted your data into an Excel table.
|21.||The “Table_array” field is the table (or array, or block of data) in which to go look for the matching PLAYERID. Because the PLAYERIDMAP sheet was previously set up as a table, we can take advantage of this.
Click once in the “Table_array” field. Simply type “PLAYERIDMAP” (no quotes). Again, this is another benefit of using Excel tables. It’s much easier to type in the table name than it is to flip tabs in Excel and select a giant range of data.
You’ll know you got this right if you see part of the PLAYERIDMAP populating to the right of the Table_array field.
|22.||The “Col_index_num” field wants to know the number of the column in the PLAYERID map table to retrieve data from.
Again, our goal is to retrieve LASTNAME from the PLAYERIDMAP table, which is column 5 in that table. You could manually flip back and forth between tabs to determine the column number. You could then just type a “5” in for “Col_index_num”.
This takes time and can be difficult if you start getting into column X (do you know what number letter of the alphabet that is?). It’s also a problem if you later add a column that would push LASTNAME into column number 6.
For these reasons, I prefer to use the COLUMN formula. When this is entered into the Col_index_num field, it will calculate the location of the LASTNAME column. We don’t have to flip back and forth and if LASTNAME gets shifted to another column, this formula will adjust automatically.
The formula below will determine that LASTNAME is the fifth column in the PLAYERIDMAP table.
Enter this equation into the Col_index_num field.
|23.||Finally, we have the Range_lookup argument. I always enter “FALSE” for this in order to ensure Excel only locates exact matches for a PLAYERID. I don’t want Excel to return an approximate match if the exact PLAYERID cannot be found. Giving me Bobby Abreu’s projection instead of Jose Abreu’s would not be helpful…|
|24.||Before hitting “OK” to accept the formula, you can preview the output. In this case we are dealing with “abreubo01″, or Bobby Abreu, and can see the formula appears to be working (look at the tip of the mouse pointer below).
Click OK to accept the formula.
Once the formula is entered, it should automatically copy to the remaining rows of the table.
|25.||We will use the VLOOKUP formula to pull additional data, and the majority of the formula will remain the same. To add a FNAME column, type the “FNAME” into cell C1. Copy the formula from cell B2. Don’t click and drag it. Actually right-click on B2 to copy it or use the CTRL + C shortcut.
Paste the formula into cell C2. When you copy and paste the formula this way you should end up with two columns displaying player last names. That’s what you want for now!
Then simply double click in cell C2 and change the column name (remember column names are surrounded in [brackets]. So change [LASTNAME] to [FIRSTNAME].).
Nerdy Excel talk here, but dragging formulas within tables does not work very well because there’s no way to make the formulas absolute (they want to stay relative as you move them). That’s why I suggest copying and pasting the formula, even if it duplicates and you then need to change part of it.
|26.||Repeat step 25 to pull TEAM, POS, and IDFANGRAPHS from the PLAYERIDMAP tab.
If you can’t remember the exact name of a column, once you have entered a table name, like PLAYERIDMAP, and the opening “[” bracket, a helpful type ahead box will display all the column names in the PLAYERIDMAP table.
You can use the mouse to double-click on the column name in the type ahead list or select a column name and hit the Tab key to add it to your formula.
The exact formulas you want are:
|27.||We’ve now pulled all the relevant information from PLAYERIDMAP and can begin pulling in hitter projections from our STEAMER_H table.
Add column headers for where we can pull plate appearances (PA), at bats (AB), hits (H), home runs (HR), runs (R), runs batted in (RBI), walks (BB), strikeouts (SO), stolen bases (SB), and any other counting stat categories used in your league’s scoring system (e.g 2B, 3B, CS, etc.).
There will be some tweaks to the VLOOKUP formula we used earlier. First, the Steamer projections use Fangraphs ID numbers. So we will be using the IDFANGRAPHS column as the Lookup_value.
Second, we must specify to pull from the Steamer Hitters projections (STEAMER_H table name).
And finally, the COLUMN formula will change to determine what statistic to pull.
Your final Plate Appearance formula should be:
|28.||After you accept the formula above, you may encounter a situation where the VLOOKUP formula will return “#N/A” for certain players.This error means that Excel could not find the value you told it to look up. In this example image below, Garrett Atkin’s player ID was not found in the projections (probably because he’s retired).
These errors can cause big problems with the rankings. It would be better to have the PA, HR, and RBI for a player to be zero or blank.
To accomplish this, we can use the “IFERROR” formula and wrap it around our original VLOOKUP formula.
This is the original formula in the cell:
Now surround the VLOOKUP formula with the IFERROR formula. The original VLOOKUP formula will remain exactly as is. I will just put “IFERROR(” in front of it. And behind the existing formula I will put “,0)”.
You can see the edits I made in the red bolded font. That’s all you have to do. Excel will now perform the exact same calculation as before, but if a player cannot be located in the VLOOKUP, Excel will put 0 instead of #N/A.
I recommend using a 0, but if you want it to be a blank instead of a zero, use this:
|29.||The formulas for AB, H, 2B, 3B, HR, R, RBI, BB, SO, and SB are shown below.AB:
|30.||I don’t pull batting average or any other rate statistics (OBP, SLG, etc.) directly from the projections. I prefer to recalculate them with a formula after the underlying statistics have been pulled.
To do this, type “BA” in as a column header. Then enter the formula to calculate batting average (=[@H]/[@AB]).
If you’re not exactly sure of the convention for the formulas you can always use your mouse to click on the values in the “H” and “AB” columns and Excel will translate it into the =[@H]/[@AB] format for you.
Repeat this step for any other rate statistics you desire (OBP, SLG, OPS, etc.).
You might notice that these rate formulas can also result in errors if a player does not have a projection (dividing by 0 causes an error). You can use the IFERROR formula around the calculation to clean this up.
Here are the formulas I use for AVG, OBP, and SLG:
|31.||Most statistics don’t need formatting, but AVG and the other rate statistics will. In my file, AVG is located in column “S”. Click on the “S” column label (or whatever column AVG is in) to select the entire AVG column. Then on the Home tab of the ribbon, click on the comma number format.
Finally, click on the symbol to increase the decimal places from two to three (the standard batting average format).
|32.||Save the file.
You’ve now successfully combined hitter information and projected hitting stats into one table.
At this point we have pulled the hitter projections into our rankings tab that we will eventually calculate each player’s projected points on.
In the next part of the series we will follow these exact same steps with pitchers in order to develop our pitcher rankings worksheet. You can check out part four here or see all parts of the series in one place here.
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.