Excel Queries and Pivot Tables
- Due Oct 12, 2015 by 9am
- Points 10
- Submitting a file upload
- File Types xls and xlsx
- Available after Oct 8, 2015 at 9am
Download the data: Excel Sheets.zip
1. [Ungraded, you can do this part of the question if you wish using the user_type.csv and the log_data.csv files in the Unmatched folder.] Use the INDEX MATCH procedure to join the user_type with the log data. Use the IF statement to include "user" as the user_type for users who are not managers or volunteers. This follows the procedure provided in the in-class handout: ExcelQueries.pdf You will have to slightly edit the formula without the formatting (just fixing which rows it points to and removing a space that comes into the "user" making it show up as "use r").
2. Create a pivot table on a new tab that summarizes posts by managers, volunteers, and regular users by month. You should include the action_type in a filter, so that one can include or exclude post or create account events and have the pivot table recalculate.
3. Create a time series graph of managers, volunteers, and regular users posts over time. This one will have three lines on one graph.
4. Create a time series of "create account" events over time. This one will have a single line on one graph.
Submit your excel sheet showing four tabs: log_data, pivot table, graph 1 and graph 2.
If you do start from scratch and do INDEX MATCH, your excel sheet should have five tabs: log_data, user_types, pivot table, graph 1, and graph 2.
Rubric
Criteria | Ratings | Pts | ||
---|---|---|---|---|
Q1 (Ungraded)
Everyone gets points for this.
threshold:
pts
|
|
pts
--
|
||
Q2
Is the pivot table laid out correctly? Is there a filter for action_type?
threshold:
pts
|
|
pts
--
|
||
Q3
Is the graph correct? (Does it depend on a pivot table that is updated incorrectly by Q4?) Are there three lines?
threshold:
pts
|
|
pts
--
|
||
Q4
Is the graph correct?
threshold:
pts
|
|
pts
--
|
||
Total Points:
10
out of 10
|