Name______Box#____
Date______per______
EGN 1006 – Major League Baseball and Excel
Purpose: To experiment with the functionality of Excel and its applications for sports statistics
Major League Baseball is a sport that has a significant following and fans around the entire globe. Every year players are recognized by how they play the game and this recognition is a direct result of calculated statistics. A player’s batting average, for example, is a significant statistic that many people talk about at the local water cooler. As you may imagine, any player with a HIGH batting average at the time is considered to be one of the better players in the league. We can also use statistics to monitor a player’s progress over the course of 162 regular season games.
Below is a legend to understand the SYMBOLS used in baseball statistics.
2B / Doubles / Made it to second base immediately after hit3B / Triples / Made it to third base immediately after hit
HR / Home Runs / Made it to home plate immediately after hit
H / Hits / Made it to at least first base
AB / At-bats / # of times a player(s) comes to bat
Avg / Batting average / # of H divided by AB
BB / Bases on balls / Getting to base without a hit, pitcher threw four non-strikes
CS / Caught stealing / Attempting to get a free base but were thrown out
SB / Stolen Bases / Getting a free base
SF / Sacrifice Flies / When a player pops the ball up hoping to move another player farther down the bases
HBP / Hit by pitch / Getting a free base when the pitcher hits a player
TB / Total Bases / H + (2B*2) +(3B times 3) + (HR times 4)
OBP / On base percentage / (H + BB+HBP) divided by (AB + BB + HBP +SF)
Slg / Slugging Percentage / TB divided by AB
SecA / Secondary Average / A way to look at a player's EXTRA bases gained, independant of batting average: (TB-H+BB+SB-CS) divided by AB
Prepare a spreadsheet using the data on the next page. Select at least SIX TEAMS of your choice. Prepare NEW COLUMNS for the following using functions:
· Total Bases
· Average (Batting) to three decimal places
· On Base percentage to three decimal places
· Slugging Percentage to three decimal places
· Secondary Average to three decimal places
TEAM / AB / H / 2B / 3B / HR / SB / SF / HBP / BB / CSBoston / 3972 / 1128 / 242 / 16 / 137 / 35 / 37 / 35 / 469 / 7
Texas / 4098 / 1114 / 221 / 20 / 193 / 42 / 19 / 34 / 361 / 12
NY Yankees / 3952 / 1087 / 193 / 10 / 162 / 61 / 33 / 52 / 449 / 21
Cincinnati / 3949 / 1039 / 247 / 12 / 159 / 56 / 23 / 45 / 384 / 27
St. Louis / 3969 / 1074 / 213 / 20 / 128 / 52 / 27 / 42 / 413 / 16
Toronto / 3977 / 1080 / 214 / 33 / 110 / 23 / 30 / 35 / 393 / 16
Oakland / 3992 / 1066 / 224 / 17 / 102 / 45 / 38 / 65 / 341 / 26
Chicago Sox / 3845 / 1007 / 170 / 10 / 146 / 62 / 34 / 30 / 372 / 20
Cleveland / 4027 / 1085 / 236 / 24 / 131 / 114 / 40 / 55 / 313 / 52
Baltimore / 3960 / 1091 / 212 / 22 / 150 / 45 / 35 / 39 / 353 / 28
Colorado / 3993 / 1063 / 208 / 24 / 109 / 7 / 33 / 33 / 435 / 17
Minnesota / 3989 / 1035 / 198 / 22 / 100 / 12 / 34 / 34 / 357 / 33
LA Dodgers / 3911 / 1007 / 199 / 12 / 116 / 21 / 24 / 24 / 371 / 24
San Diego / 3966 / 1033 / 195 / 29 / 100 / 27 / 31 / 31 / 335 / 35
Houston / 3921 / 1013 / 215 / 24 / 110 / 16 / 33 / 33 / 418 / 31
Seattle / 3911 / 999 / 204 / 22 / 95 / 16 / 29 / 29 / 315 / 34
Pittsburgh / 3990 / 1026 / 213 / 29 / 96 / 26 / 34 / 34 / 348 / 24
Kansas City / 3864 / 1007 / 211 / 26 / 91 / 20 / 29 / 29 / 293 / 26
San Francisco / 3907 / 1034 / 217 / 14 / 87 / 52 / 29 / 29 / 317 / 27
Washington / 3862 / 975 / 218 / 24 / 85 / 28 / 32 / 32 / 355 / 32
Tampa Bay / 4037 / 1114 / 210 / 30 / 115 / 62 / 34 / 30 / 374 / 20
Philadelphia / 3962 / 1051 / 185 / 23 / 114 / 79 / 27 / 41 / 444 / 23
NY Mets / 3947 / 1034 / 210 / 20 / 121 / 108 / 30 / 32 / 347 / 26
Florida / 3957 / 1087 / 224 / 22 / 96 / 66 / 39 / 48 / 356 / 30
Milwaukee / 3942 / 1013 / 240 / 7 / 130 / 62 / 24 / 56 / 384 / 23
Detroit / 3943 / 1073 / 204 / 35 / 104 / 56 / 41 / 42 / 293 / 21
Chicago Cubs / 4047 / 1101 / 241 / 17 / 143 / 48 / 24 / 35 / 305 / 28
Arizona / 4047 / 1050 / 218 / 20 / 138 / 48 / 23 / 45 / 355 / 26
Questions and Analysis:
1. Create a cell to calculate the average # of players from the teams you choose who stole a base. What is this value?
2. Create a column that calculates the percentage of home runs compared to # of hits. Which team had the highest percentage?
3. Which team had the largest slugging percentage?
4. Which team had the largest # of total bases touched?
5. Which team had the smallest On Base percentage?
6. Which team had the largest Secondary Average? Smallest?
7. Let’s say we want to know the likelihood that a player would step up to the plate and get hit? How would you go about solving this? Create a cell to calculate this for each team and list the AVERAGE percentage overall.
8. Using the Chart Wizard, construct a graph comparing the 2B,3B, and HR for the teams.