The file P02_18.xlsx contains daily values of the S&P Index from 1970 to mid-2015. It also contains percentage changes in the index from each day to the next. Create a pivot table with average of % Change in the Values area and Date in the Rows area. You will see every single date, with no real averaging taking place. This problem lets you explore how you can group naturally on a date variable. For each part below, explain the result briefly. (Note that if you are using Excel 2016, grouping by date will be done automatically.) a. Group by Month. b. Group by Year. c. Group by Month and Year (select both in the Group dialog box). Can you make it show the year averages from part b?
The file P02_18.xlsx contains daily values of the S&P Index from 1970 to mid-2015. It also
contains percentage changes in the index from each day to the next. Create a pivot table with
average of % Change in the Values area and Date in the Rows area. You will see every single
date, with no real averaging taking place. This problem lets you explore how you can group
naturally on a date variable. For each part below, explain the result briefly. (Note that if you are
using Excel 2016, grouping by date will be done automatically.)
a. Group by Month.
b. Group by Year.
c. Group by Month and Year (select both in the Group dialog box). Can you make it show the
year averages from part b?
d. Group by Quarter.
e. Group by Month and Quarter. Can you make it show the averages from part c?
f. Group by Quarter and Year.
g. Group by Month, Quarter, and Year.
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 11 images
