Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 2: USING EXCEL FOR DECISION SUPPORT
Pivot Table Analyses
Use pivot tables to analyze two sets of data: (1) Player performances at the age of 27 versus those in other
years, and (2) team batting averages this year and last year.
Is offensive performance best when the player is 27 years old?
Assume that OPS is the best performance measure to use. Using this year
s data, create a pivot table on a
separate sheet that shows the average OPS by player age. (For example, show the average OPS for all players
who are 25 years old, 26, 27, and so on.) The table should also display a count of players at each age. Player
Age should be the row label value. The Average OPS and Number of Players are Value fields. In the pivot
table, the Player Age column should have a drop-down arrow; use it to set a Value Filter that shows the top
eight OPS values by Player Age. In the pivot table, right-click the top value cell, and then sort from largest to
smallest. The player age with the best average OPS will appear at the top row of the pivot table values.
Apply the same procedure for last year
s values. Copy the table data and paste it into the sheet that
contains this year
s pivot table data so you can compare the two years. Below the tables, insert a note that states
your conclusion about the 27th year rule
does it apply or not? The data shown in Figure 11-9 is for
illustrative purposes only; your data will look slightly different.
Performance by player age
Note that the same number of players last year were ages 32 and 31, so Excel turned the top eight into
the top nine.
What were team batting averages this year versus last year? Which teams improved their batting
average this year?
Using separate sheets for each year, create pivot tables that show the composite batting average of each team.
In other words, sort the players by team, and then compile one team average from the individual player
averages. Sort the data alphabetically by team name. Copy last year
s data to the sheet that contains this
s pivot table for comparative purposes. Using Excel formulas, compute the difference in averages for each
team. Using an Excel IF() function, show which teams had a better batting average this year than last year.
Your results should look like those in Figure 11-10.
Team batting averages in each year