How to Create a Position Cheat Sheet in 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”.

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.

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”.

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.”.

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.

Start another column. For me, that’s column F. Type “Rank”.

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:

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”.

To start this process type “POS RANK” in cell G1.

Then enter this formula in cell G2:

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.

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.

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.

To prevent errors, we’ll use IFERROR again:

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.

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.