Welcome to the fourth part in a series of posts in which I’ll go through the process of plugging Steamer’s Rest of Season (RoS) projections into your existing ranking/dollar value spreadsheet so you can make informed and objective roster decisions during the season.
If you register as an SFBB Insider (it’s free), you can receive the entire series in an easy-to-use e-book (also free) along with two other helpful guides. I’ve also written a comprehensive guide on ranking players and calculating player dollar values that’s available at Amazon.
Introduction
In the fourth part of the series we’ll introduce a new Excel formula to help remove lookup errors, like those shown above, from our spreadsheets. These occur when we have instructed Excel to do a VLOOKUP to find a player’s RoS projections and Excel is unable to find the player ID within the projection data.
Often times a player will stop appearing in the RoS projections. This might be because they’ve suffered a season-ending injury, they’ve retired, or they’re an unsigned free agent. If that player remains in the list of hitters or pitchers rankings, no projection can be found for that player. I’ve realized that the rankings and dollar value formulas I previously used did not handle these situations very well, so it’s necessary to adjust these formulas slightly.
Adding this formula to your spreadsheet is a one-time fix. You won’t need to go through this part when you download updated RoS projections in the future.
Excel Functions in Part 4
IFERROR
The IFFERROR function allows us to control what happens when another function being used is calculating an error. The image below is a great example of this. On our “Hitter Ranks” tab we have a series of VLOOKUP formulas that instruct excel to go find Kendrys Morales’ player ID (moralke01) in the “Steamer Projections” tab. During the 2014 season Morales is likely not included in the RoS projections because he remains unsigned by any Major League team.
The IFFERROR 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”.
The formula requires two inputs:
IFERROR(value,value_if_error)
- 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”, returns 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.
Step-By-Step Instructions
| Step | Description |
|---|---|
| 1. | Perform the following steps for all columns on the “Hitter Ranks” and “Pitcher Ranks” tabs that pull from the projections information. If you have followed the standard instructions this would be columns G through P on the “Hitter Ranks” and columns G through P on the “Pitcher Ranks” tabs.This works best if you are able to locate a player that is pulling through “#N/A” messages instead of projections.Click on one of the cells with an error message. In the example below, I’m using cell G118. |
| 2. | Here is the original formula in the cell:
=VLOOKUP([@IDFANGRAPHS],STEAMER_H, I will now surround the VLOOKUP formula with the IFERROR formula. This original formula will remain exactly as is. I will just put “IFERROR(“ in front of it. And behind the existing formula I will put “,0)”. =IFERROR(VLOOKUP([@IDFANGRAPHS],STEAMER_H, 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. |
| 3. | Because we are using Excel’s table features, as soon as you change the formula once in a column, all the remaining formulas in the column will immediately update too. |
| 4. | Perform the steps above for each of the following hitter statistics:
|
| 5. | Use the same strategy of surrounding the existing VLOOKUP formula with the IFERROR formula on the “Pitcher Ranks” tab. Perform the steps above for each of the following pitching statistics:
|
LINK TO DOWNLOAD EXAMPLE EXCEL FILE – RoS-Rankings-and-Dollar-Values-Part-4.xlsx
QUESTIONS?
Do you have questions about Part 4? Or want to see what others have asked? Comment below.
[…] can work around the new “#VALUE!” error with the IFERROR formula. I’ve written about this before and you may recall that IFERROR’s power is that it let’s you enter a formula (like a […]