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.

—

FIGURE 11-9

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

year

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.

’

FIGURE 11-10

Team batting averages in each year