1. Visit the Auction Calculator and download a set of dollar values specific to your league’s settings. When you’ve completed the setup, click the “Export Data” link and a “Fangraphs Leaderboard.csv” will download to your computer.

2. Locate and open the “Fangraphs Leaderboard.csv” file in Excel (or your spreadsheet application of choice). Depending on your computer’s settings, you may need to right-click on the file and choose to “Open with>Excel”. After opening the file, perform a “Save As” and save the file to an Excel file format (e.g. “.xlsx”). Finally, rename the worksheet. Right-click on the lone worksheet tab and choose the “Rename” option. Give it a name like “Rankings”.

3. For the sake of simplicity, I’m going to delete some of the information from the spreadsheet that I won’t be using (the PA, mAVG, mR, mSB, mHR, PTS, and aPOS columns). Not that these aren’t helpful, but I’ve got limited real estate to work with on this web page. You can definitely keep these columns in your file.

4. The next issue we have to address is the fact that some players have multi-position eligibility. Take Buster Posey for example. You can see from the image above that he’s eligible at C and 1B. This doesn’t work well with cheat sheets and it could be argued that drafting a player and putting him anywhere besides his most valuable position (“MVP”) is an inefficient use of resources (dollar values are calculated assuming a player’s MVP is used). To determine each player’s MVP, start a new column. In cell E1 (or your first empty column), type “MVP”.

5. Copy the formula below and then paste it into cell E2 (or the second cell in the new column you just started). The formula assumes that cell C2 contains the position for the first player listed. =IFERROR(IF(FIND("C",C2,1)>0,"C"), IFERROR(IF(FIND("SS",C2,1)>0,"SS"), IFERROR(IF(FIND("2B",C2,1)>0,"2B"), IFERROR(IF(FIND("3B",C2,1)>0,"3B"), IFERROR(IF(FIND("OF",C2,1)>0,"OF"), IFERROR(IF(FIND("1B",C2,1)>0,"1B"),"DH")))))) What is this doing? The formula first searches the text in the POS column (using the FIND function) for “C”. If it doesn’t find “C”, it proceeds through the remaining positions in order of decreasing scarcity. I generally assume the order of scarcity to be C, SS, 2B, 3B, OF, and 1B. If you disagree, just swap the positions in the formula into your order of preference. The “IFERROR” functions are needed because if the “FIND” function is not able to locate “C”, the result of the formula would be an error. These layered “IFERROR” functions are essentially saying, “look for ‘C’, but if you get an error, then look for ‘SS’, and if you get an error there, then look for ‘2B’, etc.”.

6. After adding the formula to cell E2, copy the formula to the remaining cells in that column. The easiest way to do this is to click once again on E2 in order to select it. Then double-click on the small square that will appear in the lower right-hand corner of the cell.

7. Next we need to determine positional rank. I’d ultimately like to see “OF-1” for Mike Trout, so we need a formula to determine that he is in fact the first ranked outfielder. Start another column. For me, that’s column F. Type “Rank”.

8. Enter the following formula in cell F2: =COUNTIF(E$2:E2,E2) Then repeat the same process of double-clicking on the box in the corner of cell F2 to copy that formula to the remaining cells in the column. When you’re done, it should look something like this: This column is a “positional ranking”. Trout is the first outfielder. Harper the second. Goldschmidt the first 1B. Altuve the first 2B. Then Betts is the third outfielder. You get the idea. In the example file I’m working with, here’s what that COUNTIF formula turned in to for Giancarlo Stanton: =COUNTIF(E$2:E9,E9) That “$” in the formula is using an “absolute” cell reference, meaning as the formula is copied to nearby cells, that part of the formula does not change. The “$” being in front of the “2” means it will stay locked on the second row. The rest of the formula is missing the “$” so those cell references are adjusting as the formula is copied. For Stanton, this COUNTIF formula is counting any item between cells E2 and E9 that meets a specific condition… That condition is “E9”. Meaning, “count any player between E2 and E9 that is also an OF”.

9. Next, we’ll combine the player’s MVP and positional ranking to make a better looking ranking. Going back to Stanton, I’d like his rank to display as “OF-4” in one place. To start this process type “POS RANK” in cell G1. Then enter this formula in cell G2: =E2&"-"&F2 This should result in “OF-1” for Mike Trout. Using the ampersand is a shortcut to using Excel’s CONCATENATE function, which allows you to attach strings of text together. It’s a heck of a lot easier than typing that long word… This formula is appending the POS (“OF”), a dash (“-“), and the Rank (“1”) for Trout. Complete this step by copying the “POS RANK” formula to the other cells in that column.

10. That completes the work on the “Rankings” sheet. Start a new worksheet and name this “Cheat Sheet”.

11. Fill out the new “Cheat Sheet” tab as shown below.

12. Enter the following formula in cell B2: =MATCH(B$1&"-"&$A2,Rankings!$G:$G,0) NOTE: This assumes you labelled your first sheet, “Rankings”. There are many “$” in there and they’re all important. We’ll eventually be copying this formula to the rest of this “Cheat Sheet” tab and need to be careful about the cell references and how they’ll change when that happens. This formula results in “8” in my example spreadsheet. The MATCH function will look in a designated row or column and tell you which item in that row or column represents the match. This particular formula is looking in column G of our “Rankings” sheet for the string “C-1″ (that’s what the B$1&”-“&$A2 evaulates to (remember the ampersand can string text together). The final element of the MATCH formula is the zero, which means I’ve instructed Excel to find exact matches only. I only want “C-1”. If it were to find “C-11”, I don’t want that to be considered a potential match. Looking at my Rankings sheet, Posey is C-1 and is the eighth item in column G. The formula appears to be working.

13. Now add a little more to the formula in cell B2. The pieces to add are in brown text and the pieces you’ve previously added are shaded gray): = INDEX(Rankings!$A:$G, MATCH(B$1&"-"&$A2,Rankings!$G:$G,0) ,1) What we’re doing is using the combination of the INDEX and MATCH functions to find player names. This INDEX and MATCH strategy is similar to using VLOOKUP, but it’s even more powerful and flexible. INDEX wants three inputs: The area to look in (a set of rows and columns, usually). This is the “Rankings!$A:$G” piece (look in columns A through G on the “Rankings” tab).

The row number in that area that you want to pull from. This is where we use MATCH. We already used MATCH to determine Buster Posey (or “C-1” was on row 8).

The column number you want to pull from. In this case we want the “PlayerName”, which is in column 1.

14. We now just need to add one more component to that formula. It’s working for C-1. But we eventually will need to build this out to accomodate “OF-60”. And the Auction Calculator isn’t going to spit out “1B-60” or “DH-60”. To prevent errors, we’ll use IFERROR again: = IFERROR( INDEX(Rankings!$A:$G, MATCH(B$1&"-"&$A2,Rankings!$G:$G,0),1) ,"") This can be interpreted as, “If there’s an error with the INDEX/MATCH formula, just show blank text”. That’s the “”. Now drag that formula down to fill out the rest of the “C” column. Then drag it to the right to populate the remaining positions.

15. Voila!