Constructing Histograms and Identifying Outliers
Constructing Histograms and Identifying Outliers:
- Using the Baseball_2017-2021_Spring 2022 dataset in this week’s Canvas module, complete the following. (note: Credit to Aaron Chambers for the updated data)
- You will submit your assignment as an Excel file named “baseball_lastname”.
- Please read ALL instructions prior to beginning the assignment.
Legend for abbreviations:
| Abbreviation | Meaning |
| Wins | Number of games won in that season |
| BA | Batting average (hits / total plate appearances) |
| OBP | On-Base Percentage (number of times on base / total plate appearances) |
| ERA | Earned Run Average (9*earned runs / innings pitched) |
| Cost/win | Total payroll / number of wins in that season |
- Provide the descriptive statistics for all variables (except “Year” and “Team”) on a separate worksheet. Label that worksheet “Descriptives”.
- Identify the two variables that are clearly not normally distributed, by highlighting them in yellow on the “Descriptives” worksheet.
- Construct histograms, using the Analysis ToolPak in Excel, for the two following variables. Each Histogram should include at least nine (9) bins. To the extent possible, create bins of equal width (zero to minimum will be larger). Be sure that each histogram is on a separate worksheet and that each worksheet is labeled with the variable name. I would suggest finding the range for each variable and using a nice round number for your bins.
- Wins
- Cost/win
- Calculate the z-Scores for all teams (in both years) for both Total Payroll and Cost/Win in Sheet 1. You will use column I for Total Payroll z-Scores, and column J for Cost/Win z-Scores.
- In column K of sheet 1, find the percentage below each z-Score for Cost/Win. In column L of sheet 1, find the percentage above each z-Score for Cost/Win.
- Using z-Score values of >2.33 (top 1.0%), identify all outliers for both Total Payroll and Cost/Win. Highlight those teams’ cells in green on Sheet 1 using conditional formatting.
- Identify all teams with “Wins” in the bottom 10% of the normal distribution and conditionally format those teams’ cells in red on Sheet 1.
Quality Check:
You should have four total worksheets. Sheet 1 with your Z-scores and conditional formatting, the “Descriptives” sheet with your descriptive statistics and highlighting, a “Wins” sheet with your histogram for wins, and a “Cost/win” sheet with your histogram for cost/wins.
