BaseballHQ.com

by Patrick Davitt

Click here to return to the Education page.

---

• Downloading the Data
• Naming
• VLOOKUPS
• Sorting & Filtering
• Tips & Tricks

---

We've covered the basics of Excel use, including acquiring, naming, and manipulating data with sorting, filtering and, especially, VLOOKUPS.

Now we'll wrap up with a few extra tips, techniques and tools you can use to analyze and manipulate the data available in BHQ's web site offerings.

RANKING

A very quick and highly useful tool is the RANK worksheet function, which assigns a rank of a particular value within a range of values. The syntax is

=RANK(VALUE,RANGE,TYPE)

where VALUE is the value being ranked (usually a cell reference), RANGE is the range of data the VALUE is compared with, and TYPE is a number specifying how to do the ranking: zero or omitted ranks the list from the top down, while any non-zero value ranks from the bottom up.

For example, suppose your MLB pitcher's ERAs are in the range Q4:Q700. You can assign an ERA rank from best to worst to each pitcher. In the first empty column to the right of the data, or in an inserted blank column, go the cell in Row 4 (AD4 in the usual projection files). Enter the formula

=RANK(Q4,$Q$4:$Q$700,1)

In this cell, you will see a whole number value showing this player's rank among all pitchers by ERA, counting from low-to-high(the "1" value at the end of the formula). Copy the formula to all the cells and you will have the players ranked by ERA, without changing their order as you would with a sort.

Note that if you are using RANK for whole-number categories like SBs or RBIs, you will have a lot of ranking ties. RANK gives duplicate numbers the same rank, which affects the ranks of subsequent numbers. In a list of players' SBs, if eight players are tied for 18th place with 12 SBs, the players at 11 SBs would be ranked 26th—there would be nobody ranked 19-25 because all those spots were accounted for by the 12-SB guys.

This can matter if you use the MATCH function to find a particular ranking, since there would be multiple results at tied ranks.

MATCH and INDEX

The INDEX and MATCH functions can be used in tandem to emulate VLOOKUP without having to have your data organized in the somewhat rigid data-table form with the players in the leftmost column and no lookup possible on any other term.

Let's start with MATCH. The syntax is:

MATCH(VALUE,RANGE,TYPE)

Where VALUE is what you're looking for, RANGE is the group of cells (they have to be contiguous—no breaks) and TYPE is the specifies how Excel looks up the VALUE:

• If TYPE is 1, MATCH finds the largest value that is less than or equal to VALUE. For this option, RANGE must be pre-ordered in ascending order.
• If TYPE is 0, MATCH finds the first value exactly equal to VALUE. RANGE can be in any order. This is the TYPE we use almost all the time, because it saves having to pre-sort the data by every conceivable category.
• If TYPE is -1, MATCH finds the smallest value that is greater than or equal to VALUE. RANGE must be in descending order.

If TYPE is omitted, it is assumed to be 1. So always put that zero in there!

When you enter a MATCH formula, the result is the count in the range where the value is matched, counting top-down in a column or left-to-right in a row. If the fomula returns "17," for instance, you know that the MATCHing cell is the 17th in the RANGE.

INDEX is complementary to MATCH. The syntax is:

=INDEX(RANGE,NUMBER)

Where the RANGE is the column or row in which the data are found, and NUMBER is the cell in the range, counting across or down. For instance, if the names of players were in the range $A$4:$A$315, you could find the 17th player in the list by entering:

=INDEX($A$4:$A$315,17)

And you'd get it. Now this is not a particularly useful tool on its own. But you can use it in conjunction with some of the other formulas. Suppose for instance you had used RANK to create a column ranking all pitchers by ERA in $B$4:$B$315. Now you can combine the MATCH and INDEX formulas:

=INDEX($A$4:$A$315,MATCH(4,$B$4:$B$315,0))

This formula first MATCHes the value "4" in the range where the ERA ranks are to find the 4th-ranked ERA. Then INDEX looks in the pitchers' names and returns the name in that row. Note that where this formula has "4", you could use a cell reference with a "4" in it–or you could have a column with the numbers 1 through 10, to get the top 10 ERAs.

Then copy the formula using the cell references for those numbers 1-10 in the place of the "4" in the original formula. You will have a list of the top 10 pitchers by ERA.

As we mentioned earlier, if you RANK categories of whole numbers like HR or Wins, you will get ties. That means the attempt to INDEX will not work because there will be multiple entries that MATCH a particular rank, and no entries for the ranks below the tied. For example, if three players are tied for 7th in HR, there will be no 8th or 9th place rankings, and an N/A error if you attempt to MATCH 8 or 9, directly or indirectly through cell reference.

One quick workaround way to fix this is to add a very small number to each value in the range of fixed-number values like HR or Wins. You can do this by making a column (call it HR2) in which you have the formula:

=[Cell Ref for HR]+(.000001*row())

The ROW() formula returns the number value of the ROW in which the cell is found.

In the projection files, the projected HRs starts at the cell AQ5 and continue down the AQ column. Attempting to RANK the projected HRs won't work because so many players tie at various numbers—there are half a dozen at 27.

So, way over in the first empty column to the right (column BK), go to the cell in the row with the first player (should be BK5) and enter:

=AQ5+(.00001*Row())

and copy that formula down the BK column. What you'll get is a RANK list that is MATCHable and INDEXable because there are no ties. The values of the HRs (or Wins or other whole-number categories) will have those small additions corresponding to their row numbers. Since no two row numbers can be alike, no two whole-number values can be alike, and every value in the list is now unique.

(You don't have to see the added small values. You can change the number formatting in the Format|Cells menu or just narrow the column.)

CONCLUSION

There are many, many more very useful applications of Excel formulas and techniques. We've really only scratched the surface in this series.

The best way to add to your own knowledge and find cool tools is to go in there and mess around. Ask yourself, "What would I like to see or do?" and then use the Excel Help or one of the many books out there to figure out how to do it.

Finally, if you have a good tip or a question about using Excel to do something, the most valuable resource of all is the HQ Subscriber Forums. There is some serious math and Excel brainpower on those boards. Let's make it a rule that if you have an Excel question, post it in the Clubhouse section and put "EXCEL" (all capital letters) in the title.