How to Create a Position Cheat Sheet in Excel by Tanner Bell February 26, 2016 I’m going to try something a bit different here. Instead of analysis, let’s try an instructional piece about how to add a feature to your draft prep spreadsheet. In the instructions that follow, I’ll show you how to make a cheat sheet based on auction values. When you’re done, you’ll have something like this… Be Sure to Let Me Know… Making your own spreadsheet is a bit niche. Discussing lengthy Excel formulas, a bit more niche. So there’s a chance this won’t be well received. But I know you folks are out there and I have a feeling those making their own fantasy spreadsheets are likely to be hanging out here at Rotographs. I’m hoping there’s an interest in this topic. So be sure to let me know if this hits the mark and if it’s something I should consider for future posts. NOTE: I will be using Microsoft Excel 2013 to demonstrate this process. I believe all the formulas will also work in previous versions of Excel, Google Sheets, and other free Excel alternatives, but there may be subtle differences. I also assume you have some familiarity with spreadsheets and this is not your first rodeo. The instructions below start with a download from the Auction Calculator, but you could start with dollar values of any sort. You’ll just need to use some creativity to adjust the formulas according to the setup of your own spreadsheet. Step-by-Step Instructions 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! 16. If you want to make it look pretty, click the “Format as Table” button on Excel’s Home tab and choose a color pattern. Excel offers many beautiful colors… but not “Rotographs Brown”. I had to make my own. I only set this up to show the top 20 at each position, but the beauty of the formulas used is that you can easily expand this by dragging the formulas down as far as you want. The only limitation is the number of players exported from the Auction Calculator. Wrapping Up, Want More? Was this helpful? Interesting? I’d love to hear your reaction in the comments. Would you like to see more posts like this in the future? Hopefully your feedback will be positive and I’ll be back with more. But in the meantime, if you’re looking for more information on building a draft or rankings spreadsheet, here are some other instructions I’ve written previously: How to Track Drafted Players in a Spreadsheet – This set of instructions guides you through adding a drop down menu that lists each team in your league. You can quickly use this drop down during the draft to mark off drafted players and assign them to teams in the league. We added something similar to this in the first edition of the Rotographs Rankings Spreadsheet. How to Automatically Shade Drafted Players in Excel – This picks up where the first bullet leaves off. Once a player is marked as drafted, you can have Excel shade the player’s name out or you could strike-through the information like we also added to the Rotographs Rankings Spreadsheet. How to Calculate Projected Standings for your Draft – Several weeks ago I wrote about Why You Should Aim for Third Place and the idea that this may be the most efficient use of resources during a draft. In the comments, some grew skeptical about how practical this really is, questioning how you can feasibly track where you fall in the standings while the draft is ongoing. This guide should help you set up your spreadsheet so you can see updated standings in just a few mouse clicks. The first bullet above is also a prerequisite for this one to work properly.