Hey if you have time PEDs could you explain me about what FLD and stuff I am putting in or
Just help me do this.
OK, let’s do one together.
1) Open a clean Excel spread sheet.
2) Click the following link (
http://www.fangraphs.com/leaders.aspx?p ... 09&month=0), click ‘Export to Excel,’ click ‘Open,’ and copy/paste it to your clean Excel sheet.
3) Close the fangrpahs.com window, go back to your excel sheet and delete every column except: Name, RF/9, RngR and UZR/150. You might want to keep the other columns in the future, but let’s keep it clean and simple.
4) Insert two new columns after RF/9, RngR and UZR/150 and name them RF/9z and RF/9x, RngRz and RngRx, 150z and 150x.
So, your excel spread sheet should currently have columns A (Name) through J (150x) and rows 1 (Name) through 20 (Vernon Wells).
5) Highlight cell B21 (under the last RF/9 score), click the arrow next to the AutoSum button (the Sigma symbol), click ‘Average’ and hit Enter.
When you highlight the cell you should see: =AVERAGE(B2:B20). You can also enter the functions manually.
6) Now highlight cell B22 (under RF/9 average), click the arrow next to the AutoSum button, click ‘More Functions,’ find and click STDV, make sure the range is B2:B20, and then click Enter. I enter STDEV manually to avoid the mouse clicking
You should see this in the function window: =STDEV(B2:B20).
7) Copy/paste cells B21 and B22 under the other two columns of data, RngR and UZR/150. Now we have averages and stdevs for all three columns.
8] Highlight cell C2 and enter the following function in the fx window: =SUM(B2-$B$21)/$B$22. In other words, subtract Franklin Gutierrez’s RF/9 score from the RF/9 average, and then divide by the stdev. The $ symbol locks that specific part of the function so we can apply to the other players without having to re-enter the function. So highlight cell C2 again, hold/drag it down to cell C20 and click Enter. Now we have RF/9z scores for all of the players.
9) Highlight all of the data in rows 1 through 20 (not 21 and 22) and click: Data / Sort / Sort by RF/9z / Descending / OK. The players should now be ranked in descending order.
10) Highlight cell D2 and enter the following function in the fx window: =SUM(C2-$C$20). In other words, subtract Adam Jones’ RF/9z score from the lowest RF/9z score. Again, we used the $ symbol so we can subtract everyone’s RF/9z score from the lowest RF/9z score. So highlight cell D2, hold/drag it down to cell D20 and click Enter.
11) There is one last step before we move to RngR. Because we will be sorting again, our previous functions will get screwed up as things move out of order. The easiest way to avoid this is to copy our created data (C2 through D20), paste it to a message board window (seriously – a reply window right here at MLBPP or at fangraphs will do the trick), copy it from the message board window, and paste it back over the top on our excel sheet. Now our numbers are hard instead of the being reliant on the functions which created them.
12) Repeat steps 8 through 11 for RngR and UZR/150.
13) Add two more columns after 150x, and name them Score and FLD. Use 0 decimal places for the FLD column.
14) Highlight cell K2 (Score) and enter the following function: =AVERAGE(D2,G2,J2). Highlight cell K2 and hold/drag it down to K20. In other words, average the three x-scores for each player.
15) Highlight all of the data in rows 1 through 20 (not 21 and 22) and click: Data / Sort / Sort by Score / Descending / OK.
16) Now it’s time to turn these numbers into our FLD ratings. As you can see, Franklin Gutierrez is the highest rated defensive CF by a sizeable margin. He is currently one of the premier defenders in baseball, regardless of position, and probably deserves a 15 rating for his glove, range and reaction time. As the highest rated player, he is out benchmark.
NOTE: The highest rated player will usually be the benchmark when calculating this way. For roto baseball, the benchmark is actually the lowest ranked draftable player at each position, but we aren’t using dollars here, we are using a 15-point scale.
So . . . highlight cell L21 and enter: =SUM(15/K2). K2 is Franklin Gutierrez’s Score and the number 15 represents his FLD rating.
17) Highlight cell L2 and enter the following function: =SUM(K2*$L$21). Highlight cell L2 and hold/drag it down to L20. Bingo!
That’s how you do it. Now, in this example you’ll notice Vernon Wells has a FLD rating of 0. That’s because he was the worst in all three categories in our small sample of players. That won’t happen when you increase your sample to include more players.
The key is deciding who your benchmark is and what his rating should be. For example, maybe you believe Gutierrez has been so good, he is tipping the scales and everyone not named Gutierrez should have higher FLD ratings. No problem. Same process as step 17:
Highlight cell L21 and enter =SUM(14/K3). In other words, K3 is Carlos Gomez’s Score and the number 14 represents his FLD rating. Then in cell L3, enter: =SUM(K3*$L$21). Highlight cell L2 and hold/drag it down to L20. As you can see, most of the CF’s received a 1-point bump to their FLD ratings.
Again, because our sample size was small, guys like Shane Victorino and Jacoby Ellsbury got the shaft. They move up the scale once you increase the sample size to include all OF’s, so don’t use these as your actual ratings. You can go down the list and manually increase/decrease the ratings as you see fit, but with enough innings, this is a pretty accurate indicator of a player’s true defensive ability. Guys like Victorino and Ellsbury are deceiving. They appear to be excellent defenders due to their blazing speed, but their speed is often nullified by below average reaction time, misreads, and poor route running.
If you feel there guys are ranked too low, go ahead and do away with RF/9 and just use the other two. Also, pay attention to the Innings. A crappy 3B may have had a great 150 innings at SS but that doesn't make him a great SS. I suggest you break your players up into 2 or 3 groups based on innings played, and use more conservative ratings for players with fewer innings.
That’s about it. The step-by-step guide makes it appear hard and time consuming, but once you get the hang of it, you can hammer these out in just a few minutes.