How To Add Positional Ranking To Your Spreadsheet

I just came across an interesting use of an Excel formula that will let you easily determine what a player’s ranking is at their given position (1B-1, 1B-2, 1B-3, etc.).

Here’s an explanation that might show what I’m talking about.  You’re scrolling through your huge list of ranked hitters (see image below).  You have them sorted by Total Standings Gain Points (column V) in descending order.

You see Edwin Encarnacion’s name pop up in row #38.  You know he’s a first basemen, and you can pretty easily determine that he must be the #37th ranked player (by showing up on row 38).  But now you want to know where he ranks amongst only other first basemen.

In this post I’ll show you a formula we can use to get our spreadsheet to look like this (look at column W):

I’m a Moron

I got nearly to the end of this post when I started to think it was weird that Encarnacion was ranked #37, Starlin Castro #32, and Paul Goldschmidt #21. Turns out I used a rankings file from the 2013 preseason for all the screenshots…

I decided against starting over because it’s not the player names that are important, we’re mostly looking at a new formula. And I found it pretty interesting and thought provoking to look at these old lists and see names like Nori Aoki and B.J. Upton so high.

Excel Formulas Used In This Post

Using the “&” to Build Text

As you can see from the image above, we’re trying to take each player’s position (e.g. “1B”) and then add a dash and then the player’s positional ranking (e.g. “1B-8” for Encarnacion).

You can use the ampersand (would you know what that was called without “Wheel of Fortune”?), in an Excel formula to add text from different columns.

A real practical application of this is to build a player’s full name (e.g Billy Hamilton) from their first name and last names being in separate columns.  Here’s an example:

I did leave something important out of this formula.  Look what happens:

Just appending the two fields together leaves out a space between the first and last name.  So to revise the formula, we can add a space between the names like this:

We’re now putting three pieces of information together.  Cell B2 plus a space (” “) plus A2.  And you can continue adding on information with more “&” symbols.

You may be familiar with Excel’s CONCATENATE function which does the same thing, but I just find this easier to use.

COUNTIF

The COUNTIF formula will count the number of cells in a specific column (or other range) that meet a specified condition.

In plain English, our goal is to have Excel count the number players that have the position of first basemen (1B) in column E.

As you can see from the function wizard above, this formula requires two inputs:

COUNTIF( Range , Criteria )

1. Range – This is the block, column, or area of cells we want to count from.  “Excel, look in Column E and count the cells that meet this condition I’m about to tell you about in bullet #2.”
2. Criteria – This is what we are evaluating the cells for.  “Count the cells in Column E that show ‘1B’ as the position.”

More About The RANGE We Want In The COUNTIF Formula

I simplified things above.  Thinking more closely about this, we don’t really want to count the total number of “1B” in Column E.  For Albert Pujols, the very first “1B” in the column, we only want him counted.  For Prince Fielder, the second “1B”, we want only him and Pujols counted.  For Paul Goldschmidt, the seventh “1B”, we want him and all those before him.  And so on.

To accomplish this we need the range of cells we’re looking in to change as we move down the player list.

Here’s a graphic of the range we want for Pujols.  Check out the highlighted cells.  We would want to count the number of 1B in cells E2 through E5.

And here’s one for the range we want for Paul Goldschmidt.  We would want to count from E2 to E22.

So the range the we want needs to be able to expand as the formula moves down the column.  The way to do this in Excel is with the concept of absolute and relative cell references.

We want the “E2” part of the range to be frozen  in place.  But we want the second part, “E5” (in “E2:E5”) or “E22” (in “E2:E22”) to move.

To do this, we will specify to Excel that the first cell in our range is “absolute” and that the second cell is “relative”.

We do this by putting a dollar sign in front of the row number of the absolute cell.  You can put the dollar sign in front of the column if you don’t want the column to change as the formula moves.

E$2:E5

As the formula is moved down the column, the dollar sign tells Excel not to move the E2 reference.

Enough Concepts.  What’s the Formula?

Have Other Questions About How To Do Things Like This?

If you have questions about making an awesome rankings or draft spreadsheet, let me know.  You can comment below or e-mail me at smartfantasybaseball at gmail dot com.

If you’re interested in similar articles in the future, following the site on Twitter or registering as an Insider are the best ways to keep up-to-date.