Player Evaluation 103 - Calculated Statistics

[Jackstraw]

Today we are going to take your file to another level. We are going to calculate the batting average, ERA, and WHIP. Later we are going to add a simple ranking system based on a rotisserie-style scoring method.



Most of the formulas for calculating baseball statistics can be found with a search of the internet. Over time, and depending on the depth of your evaluations, you are going to encounter many different statistics. Doing work in Excel with these formulas does two important things: helps you to understand the variables in the statistic better and helps you to become better at using Excel. As the face of the game begins to change, understanding these two key components becomes an advantage.



The most basic of the calculated statistics in baseball is batting average, BA. It is the number of hits that a player has divided by his number of at bats. I won’t spend any time here discussing its importance or its relativity to the game. The fact is that the Fantasy Baseball Gods have deemed it a scoring category in the traditional 5x5 game, so we need to understand it.



The most basic thing in Excel is navigating your spreadsheet. Let’s talk about it before I start introducing formulas. The first row in your spreadsheet should contain column headings. Each column heading should correspond to the different statistics. The second row is the first row of data. The form of a reference in Excel is Cartesian, where a letter is used for a column and a number is used for a row. For instance, cell C10 is the intersection of column C and row 10.



The column headings in row 1 of my spreadsheet are set like this:

First Name, Last Name, AB, H, R, HR, RBI, SB



Row 2 in the spreadsheet looks like this:

Reggie, Abercrombie, 76, 15, 16, 2, 5, 7



Assuming that hits, H, is Column D, then the cell reference for Reggie Abercrombie’s number of hits would be D2. If you select a cell off to the side of your data and type in =D2 then the number 15 will appear. To make a long story short, the cell references are the variables in your formula. So, let’s start off with calculating batting average in your hitters tab.



1. Click on the hitters tab.

2. Click on the letter of the column next to your hits (H) column. This will select the entire column.

3. Right-click over the selected column and select Insert.

4. In the first cell of the new column, in the top row, type “BA”. This is going to be our batting average column. Hit enter after you finish typing so that the active cell moves to the cell below BA.

5. Using the example of the layout I used above, type this formula into the active cell, =D2/C2. The actual formula is H/AB, so you may need to adjust the columns to match your layout. Hit Enter.

6. Now, move your mouse over the cell with the calculated batting average, where you just typed in the formula, and select it to make it active.

7. An active cell has a heavy black border around it. On the bottom right-hand corner of the active cell is a small black square. I will refer to this square as the “handle.” The handle lets you do quick copying and pasting on adjoining cells. Move your cursor over the handle and double-click.

8. The formula will automatically paste down the column to the very last entry. Scroll down to the bottom of your data and you should now have batting averages for each player.

9. Save your file now. We are modifying your modified raw data file since batting average is a standard statistic that we need to have on hand.



Now to calculate ERA and WHIP in the starters tab. Let’s assume the layout of the column headings is as follows:

First Name, Last Name, GS, IP, W, H, BB, ER, K, SV



Row 2 should look like this:

Dontrelle, Willis, 35, 205.1, 10, 241, 87, 118, 146, 0



1. Click on the starters tab and find the column where innings pitched (IP) are located.

2. Select the column next to IP and repeat the process for inserting a column. Type IP2 as the column heading for the new column and hit Enter.

3. This is going to look nasty. I’m going to type it just like it needs to be typed in the formula bar for your innings pitched. Again this reference is based on my layout and yours may need to be adjusted.



=(D2-ROUND(D2,0))*3+ROUND(D2,0)



What we’ve done here is correct a flaw in the way the data is recorded. In baseball, the inning is divided into thirds. The short-hand notation for innings pitched always uses 0.1 to represent one out and 0.2 for two outs. Somewhere along the line when people began setting up databases they chose to use the short-hand notation rather than the correct decimal form of .3 and .6. To calculate ERA and WHIP we have to get the real decimal values for the innings pitched. The short-hand will give us incorrect statistics.



4. Once your formula is typed in the formula bar, then hit Enter. The number that appears should just be a decimal conversion of innings pitched. In Dontrelle’s case the number should be 205.3 IP. If this is right, double-click on the handle to paste the data down the column.

5. Now repeat your steps to insert a column next to earned runs (ER). Type in ERA as your column heading.

6. The formula for ERA is (ER*9)/IP2, based on the way we’ve named the columns. In row 2 of your ERA column type =(I2*9)/E2. Note that when you inserted the new innings pitched column that the rest of the columns shifted to the right.

7. Hit Enter, then select the cell where you typed the formula and double-click the handle.

8. Now insert a column next to ERA and title it WHIP.

9. The formula for WHIP is (BB+H)/IP2. In the second row of the WHIP column, type in the correct formula based on the instructions given for BA and ERA.

10. Finish pasting the formulas down the column.

11. SAVE!



Whew, that was a load right there! But at least we accomplished what we set out to do, except for the relievers. I'll leave that one for you since it is just a repeat of the starters. The key is that you learned how to create some formulas, perform a few spreadsheet tricks, and navigate the cells. Your modified raw data file is now complete. Save it and stash it back somewhere. Save another copy as your play data. If you do a search you can find some other calculated statistics and create your own columns with the formulas. Just remember the variables in the formula are going to be replaced with cell references.



Now I’m going to get some shut-eye. I got an opportunity for a free ticket to see the Braves and I couldn’t pass it up. What a game… Keep an eye on Chipper. He is starting to get on a roll. I take back all of the crap I was giving him