Activity Title: Which Player Should Be Signed Using Their Stats?

The Research Experience for Teachers Program /
http://www.cs.appstate.edu/ret

Name: ______

Activity Title: Which Player Should Be Signed Using Their Stats?

Learning Objectives: Students will learn how to use Excel to find statistical data in order to analyze and make a decision based off the MLB data from 2011 – 2014.

Materials List: computers, spreadsheet software, Power Point

Introduction/Motivation: Suppose you are the assistant for a MLB (Major League Baseball) coach. For the up-coming 2015 season the team needs to sign one more player to complete the team. The team could really use a good batter so we will only be looking at each players batting statistics. The coach has already narrowed it down to his top 10 choices that he’s interested in signing. He has asked you to statistically determine which of the 10 players he should pick.

Lab Activity:

Part 1

Since several of you may not know anything about baseball, we are going to start the activity with a class discussion about what are considered a good number of runs, hits, homeruns, strikeouts, and batting averages. If you are unfamiliar with any of these, you may want to write them down below for reference:

Part 2

1.  Open the “Baseball.xlsx” Excel file.

2.  Find the mean of At Bat, Runs, Hits, Homeruns, and Strike Outs of each player. In F4 write the word Mean, bold and center it. In F5 you will write an equation for Excel to find the mean of ‘At Bat’ for the player from 2011 to 2014. To do this, in the cell write =average( then click on cell B5. Hold down the left click and drag it to cell E5. End the parenthesis and hit enter. If done correctly this should give you an average of 568.6667.

3.  Now we will format the cell so we don’t have to deal with the decimals. Click on F5 and then right click. Click on Format Cells, Number Tab, Number, and change the decimal places to 0.

4.  Now we will find the mean of the other statistics. Click on F5, put the curser on the bottom right corner and drag down to F9.

5.  Find the Mean’s for each of the other players.

6.  In G4 write Quartile 1, bold and center it. In G5 you will write an equation in Excel to find Quartile 1 of ‘At Bat’ for the player from 2011 to 2014. To do this, in the cell write =quartile( then click on B5. Hold down the left click and drag across to cell E5. Put a comma after E5 then the number 1. This tells Excel to find Quartile 1. End the parenthesis and hit enter.

7.  Click on G5, put the curser on the bottom right corner and drag down to G9.

8.  In H4 write Quartile 3, bold and center it. Repeat the same process as you did for Q1

9.  In I4 write IQR, bold and center it. In I5 you will write an equation in Excel to find the IQR “Inner Quartile Range” of ‘At Bat’ for the player from 2011 to 2014. To do this, in the cell write =( then click on H5 (Q3), minus, click on G5 (Q1). End the parenthesis and hit enter.

10.  Click on I5, put the curser on the bottom right corner and drag down to I9.

11.  Find the Quartile 1, Quartile 3, and IQR for each of the other players.

Part 3

A popular way to measure a player is by determining their batting average which we will do in Excel. Batting average (AVG) is found by taking the number of hits divided by the total times at bat.

1.  In A10 add another title called AVG and bold it. In B10 write an equation to find the AVG.

2.  Change the format of AVG so it only has 3 decimal places.

3.  Find the AVG of each year.

4.  Find the AVG of all the other players for every year they played.

5.  Save your Excel file and email to your teacher.

Part 4

Using your data, you and your partner need to determine which player you believe the coach should choose. First you need to decide what measures are the most important.

1.  What measures do you believe are the top two to determine who the best player is? Explain.

2.  Determine which player you feel the coach should sign based from the measures you chose above. You and your partner will present your choice to the class and you will need to justify why you feel the measures you chose are the top two to determine who the best player is.

3.  With your partner arrange the players in order from what you believe should be 1st choice to 5th choice. After presentations, the true order will be revealed. Let’s see how accurate you are!

Extension

1.  Open the “Baseball Batting Data 1871 to 2014” Excel file.

2.  Determine which player had the best batting average (AVG) in 1995.

Results/Conclusions

Students should email a copy of the completed Excel file to the teacher.

Reference

http://espn.go.com/mlb/statistics