Welcome to the fourth 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 almost any part of the series, but it’s not recommended you start with Part 4 unless you are very familiar with the Excel functions listed below. Part 4 is essentially reperforming step 3(which focused on hitters) for pitchers. This post assumes you are familiar with the Excel functions and formulas used in Part 3.
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 fourth part of the series we will use Excel formulas and functions to start pulling pitcher information (name, position, team) and projection information in order to eventually calculate our own rankings.
EXCEL FUNCTIONS AND FORMULAS IN THIS POST
Below are the Excel functions and formulas used in this post. If you would like more background on them, please refer to Part 3 or ask questions in the comments area below.
- VLOOKUP
- TABLES and NAMED RANGES
- COLUMN
STEP-BY-STEP INSTRUCTIONS
- Staying consistent with the hitting projections, I’m going to use the free Steamer projections from Fangraphs.
- To make the pitching projections easier to work with, convert the “Steamer Pitchers” tab to a “table” in Excel. To do this, click anywhere within the data on the “Steamer Pitchers” worksheet. 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, W, L, ERA, etc.). Check “My table has headers”. Click OK.
- Because we’ll later be these pitcher projections into other worksheets, it will help us greatly if the fangraphs player ID is the first column of the table (you can use the VLOOKUP formula 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 (should be column X in the Steamer Pitchers projections).Now right-click on the top of the player name column (column header “A”) and select “Insert Cut Cells”. When you’re done, you should have the “playerid” column first and “Name” second. (more…)