3. In column C, build a formula that gathers the average score for each month. The goal
is to report the average month in the first row for each month and then use #N/A val-
ues for the remaining rows for that month. If the dates start in A2, the formula is
=IF(A2=A1,NA(),VLOOKUP(A2,$V$4:$W$74,2,FALSE)). Figure 3.41 shows a few rows of
the dataset. Dot scores appear in the second column, and line scores appear once per
month in the third column. The results of the pivot table appear in the fourth and fifth
columns.
3
Chapter 3 Creating Charts That Show Trends
120
Figure 3.41
The underlying
detail used to create
the chart in Figure
3.40.
4. Build a chart based ...