Power Pros Forum

This is a click-by-click guide for creating CON and Vs Lefty ratings using Excel. These basic steps can be used to convert a variety of different statistics-to-abilities. The formulas posted are in reference to data I grabbed from Fangraphs several days ago. Additional players have appeared in games since, so make sure you adjust the formulas to include all players on your list when necessary.

1. Go to Fangraphs.com, click Leaders, and choose your criteria (let’s do this first one together, so click Split: vs R > Min PA: 10 > Multiple Seasons: 2011 to 2012), click Export Data to open data in Excel, and rename the Worksheet tab (bottom left where it says FanGraphs Leaderboard) as vsR and rename/save the Excel workbook as MLBPP12.

2. To remove the pitchers from our list (recommended), go back to the Fangraphs.com page and click P to show just the pitchers, click Export Data to open data in Excel, select/copy/paste data to the first empty cell in the vsR tab in our MLBPP12 workbook (should be cell A788 or something very close). Now we have the pitchers listed twice, which makes them easy to identify with the COUNTIF function. To do that, select empty cell X2 and in the fx formula bar paste:

=COUNTIF(W$2:W$1000, W2)>1

Click ENTER. Select cell X2 and copy/paste (or drag) the formula down the entire column to the last player. Give column X the header name TF. Select all (click upper-left corner of window or hold Ctrl/A), and click Data > Sort > TF > Largest to Smallest > OK. If a player is listed twice it says TRUE, and players listed twice are pitchers, so delete every player listed as TRUE. Save.

3. Go back to the Fangraphs.com page, filter Position: All, Split: vs L, click Export Data to open data in Excel, and copy/paste data to another Worksheet tab (to create a new tab, click Insert Worksheet at bottom of excel window) in our saved MLBPP12 workbook, rename the tab vsL and save the workbook again. Repeat step 2 to remove pitchers. Save.

Note: If you call it good at this point and assign ratings for CON and Vs Lefty, batters with small sample sizes will be badly skewed. So we march on . . .

4. Starting with vsR, select all (click upper-left corner of window or hold Ctrl/A), and click Data > Sort > AB > Largest to Smallest > OK. [note: AB is the At Bat column, not column between AA and AC]. Select the cell at the bottom of the AB column (D653) and click AutoSum > Average > ENTER. Repeat this step for the H column. Insert a new column directly to the right of AB column and name the header aAB. Select cell 2 of the new aAB column (should be E2 on your sheet), move your cursor to the fx formula bar and paste:

=D2/D$653

Click ENTER. Select cell E2 again and copy/paste (or drag) the formula down the entire column to the last player. Right click the top of column E > Format Cells > Custom (and under Type: enter .000) > OK. Lastly, enter 1 in cell E2 and then copy/paste it down to the last cell showing 1.000 and above. Leave cells with .999 and below alone. Save.

Note: Why did we divide ABs by the average and then replace some numbers with 1.000? We are setting a benchmark to erase the small sample size noise. For this exercise, players above the benchmark (league average number of ABs or above) have enough ABs to reflect their true CON ability. Players below the benchmark will have their CON ability adjusted to reflect true level.

5. Find an empty cell (use bottom of the aAB column) and paste:

=sum(G653/D653)*.93

Click ENTER. Save.

Note: This is the mean average for all players (H/AB) with an added replacement level component which, for batting average, is 93%). That .93 is the difference between MLBPP scrubs with 6/7/8 CON versus 5/6/7 CON. The latter is more realistic.

6. Insert a new column directly to the right of AVG column and name the header aAVG. Select cell 2 of the new aAVG column (should be X2 on your sheet), move your cursor to the fx formula bar and paste:

=SUM(E2*W2)+((1-E2)*E$653)

Click ENTER. Select cell X2 again and copy/paste (or drag) the formula down the entire column to the last player. Select all (click upper-left corner of window or hold Ctrl/A), and click Data > Sort > aAVG > Largest to Smallest > OK. Save.

7. Insert a new column directly to the right of aAVG column and name the header CON. Use aAVG to assign CON ratings to the players based on the following criteria:

CON High Low
15 .387 .360
14 .359 .348
13 .347 .336
12 .335 .323
11 .322 .308
10 .307 .293
9 .292 .278
8 .277 .263
7 .262 .248
6 .247 .233
5 .232 .218
4 .217 .200
3 .199 .180
2 .179 .160
1 .159 .000

8. Open the vsL tab and repeat steps 4 and 5. Make sure you change the formulas to reflect the location of your data. Repeat step 6, but do not sort aAVG. Repeat step 7, but instead of entering CON ratings, just enter the letter x down the entire column. Save.

9. Go to your desktop and open a new Text document (R click > New > Text Document). Copy/Paste the aAB and aAVG columns from the vsR and vsL worksheets, paste them to the Text doc, and then copy/paste them back to their original location. Save.

Note: This turns our formulas into hard numbers before we do more sorting.

10. Delete the =AVERAGE and = SUM formulas on the bottom row of each worksheet. Copy all rows of data (not the header) from the vsR worksheet and paste them to the first empty row of your vsL worksheet. We need to isolate the duplicates, so select cell Z2 (TF column) and paste this in the formula bar:

=COUNTIF(Z$2:Z$1250, Z2)>1

Click ENTER. Select cell Z2 and copy/paste (or drag) the formula down the entire column to the last player. Select all (click upper-left corner of window or hold Ctrl/A), and click Data > Sort > TF > Largest to Smallest > OK. Highlight all rows of players listed as TRUE in column TF and click Data > Sort > PlayerID > Smallest to Largest > {Add Level} > CON > Smallest to Largest > OK. Insert two empty columns directly to the right of the aAVG column. Name the header of the first vL and the second Tot. Save.

11. Select column Y2 (vL column) and paste this in the formula bar:

=X3-X2

Select column Z2 (Tot column) and paste this in the formula bar:

=SUM((X2*D2)+(X3*D3))/(D2+D3)

Select cells Y2 and Z2 and copy/paste (or drag) the formulas down to the last player listed as TRUE. Repeat the Text Document trick from step 9 to solidify the data in columns Y and Z. Select all rows listed as TRUE and click Data > Sort > CON > Smallest to Largest > OK and delete all rows where the player is listed as TRUE and has the letter x for his CON. Save.

12. Select all, Data > Sort > vL > Smallest to Largest > OK.

Note: This shows us how the batter does against LH pitchers in relation to RH pitchers. We will use a -70/-35/0/35/70 split.

Insert a new column directly to the right of vL column and name the header Vs Lefty. In cell Z2 (Vs Lefty column) enter Vs Lefty 1. Copy/paste the cell containing Vs Lefty 1 down to the last player who has a vL differential of -.070 or worse. Enter/copy/paste Vs Lefty 2 for all players between -.069 and -.035. Repeat for players on the positive end of the spectrum with Vs Lefty 4 and 5. Sweet! We are almost done. Save.

Note: We have two things to resolve. First, we need to adjust the CON ratings for hitters who don’t have a Vs Lefty ability, because as of now their CON rating is based solely on their ability to hit RHP. Luckily, we already did the math to combine R/L in the Tot column. Second, we have to finish the group of players at the bottom who received 10+ PAs against RHP or LHP, but not the other.

13. Select all (click upper-left corner of window or hold Ctrl/A), and click Data > Sort > Vs Lefty > Largest to Smallest > {Add Level} > Tot > Largest to Smallest > OK. Use the CON chart from step 7 and results in the Tot column to enter new CON ratings for players who do not have the Vs Lefty ability. Save.

14. Select rows of data for players at the bottom who received 10+ PAs against only one side of the platoon split, and click Data > Sort > {unclick the My Data Has Headers box} > column AB (CON) > Largest to Smallest > {Add Level} > column x (aAVG) > Largest to Smallest > OK. No need to research these players one by one – it’s OK to make some assumptions here. First, players with a CON rating of x (there should only be a few) have more PAs vs LHP than RHP, which means they probably struggle vs RHP. Replacement level for CON is around 6, so let's give them all 5’s. The rest of the list consists of LH specialists who struggle vs LHP and small sample size players. We already have CON ratings for this group, but we should assume that a hitter with a ton of PAs vs RHP and fewer than 10 PAs vs LHP struggles vs LHP. Give these guys Vs Lefty 2 or 1. Save.

Done!

Any formulas for power or run speed?

Yep. Wait for it . . .

In case you didn't know it, instead of having to re-post the pitchers to eliminate them, fangraphs has a column after dh that says np. that stands for non-pitchers i believe. that should speed things along somewhat and you can skip a step or two.