week 13

Excel Formulae and Charts

Initial   Setup  

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.

  1. Copy the file baseball_formulae.xls from V:\mgmt110-1 to your home drive H: (or you can right-click on baseball_formulae, choose Save Target As and save to your home drive H:\

  2. Start up Excel and open H:\baseball_formulae.xls
    (do not double-click on the file while it is on the shared drive V:. If you do this accidently, close Excel immediately and do not save).
Instructions  

Calculating Basball Statistics

  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.
    1. Which player had the 5th best SLG% ?
    2. Which guys did Bobby Bridge get better SLG% than?

  7. Charting. 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. Save the spreadsheet and email it to me.
    Save the chart as a web page (make sure chart sheet is selected) (click on 'chart' option) in your Home drive. In Windows browse to the folder that was created and open the GIF graphic of the chart. Email this to me.