You will use the excel spreadsheet baseball_formulae.xls. The instructions are available inside the spreadsheetsheet itself or you can print them out from below.

Download the file baseball_formulae.xls and save in your excel_data folder on your home drive.

Double-click on the file to start it up with MS Excel.

1. Tidy up the player's names by removing the dots …

2. Calculate batting average in column C.
   AVG = Hits, H (col G) / At Bats, AB (col E)

   • in cell C2 enter the formula =G2/H2 and copy this down the column. Notice how the formula changes

3. SUM the At-Bats column (select column and cell underneath, click on Sum icon on toolbar).
   SUM the Hits column
   Calculate the overall AVG by extending the formula in column C one more cell downwards

4. Calculate Slugging Percentage.
   SLG% = Total Bases (TB) / At Bats (AB). Need to calculate Total Bases
   Total Bases = Singles + 2 * 2B + 3 * 3B + 4 * HR . Need to calculate Singles
   Singles = Total Hits (H) - (2B + 3B + HR)

   1. Create a new column between G and H. Title it S for Singles
   2. In the new H2 cell enter the formula to calculate Singles. Use brackets to be on the safe side.
      Hint: =G2- (I2+J2+K2). Copy this down the column.
   3. Now enter a formula in the first Total Bases cell to calculate TB - use the equation above and put brackets around the multiplications. Copy this down the cells.
   4. Now you can enter the formula to calculate SLG% in column N

5. Now you can answer some "what if" questions. Enter these answers onto your spreadsheet. Record orginal values so that you can return to them.

   1. How many hits would Curt Courtad need to get a .408 Batting Average in his next 8 At Bats?
      Hint: change AB to 49 and then increase Hits until AVG=.408
   2. Using a similar technique, what combination of 2B, 3B and HR would Curt need in his next 8 AT Bats to better Kevin Carr's Slugging Percentage (SLG%) ?

6. Sorting.
   Return Curt's figures to their original values. Enter answers to the following questions into your spreadsheet
   Sort the whole table by SLG% then by Player's name. (You know how to do this, right?)

   1. Which player had the 5th best SLG% ?
   2. Which guys did Bobby Bridge get better SLG% than?

7. Let's use a chart to answer the question: Has coach picked the right starting players? Players that are at bat more frequently should be getting a higher hit rate. Let's plot Hits vs At Bats to see whether this is true.

   1. Select columns of data AB (col E) and H (col G) - use Ctrl to select this.
   2. Run the chart wizzard and select XY chart. AT Bats goes on the X-Axis and Hits on the Y-axis. Put the chart on a separate sheet.
   3. Add Titles to X ("At Bats") and Y ("Hits") axes and get rid of gridlines. Finish.
      This gives us a bunch of points. Now let's add a "trend line".
   4. Right click on a data point and select Trendline. In the options section select set intercept = 0, display equation and display R2.
   
   Question:
   • What trend line type might fit the data better? (Hint: think polynomial). Add this line to the chart.

8. Upload the modified spreadsheet into a new entry in your blog.

9. Save the chart as a web page and upload the chart GIF into a new blog entry.

   1. make sure chart sheet is selected (click on 'chart' option)
   2. Select File —> Save as Web Page and save in excel_data on your Home drive.
   3. In Windows Exploder browse to the folder that was created within excel_data and open the GIF graphic of the chart to check it's OK.
   4. Now upload this graphic into a new blog entry in the accustomed way.