Take home quiz #5 Part 1: Pivot Tables

Due Wed, Nov. 30. Worth 15 points

Make three pivot tables following the instructions below. 

In an email, submit your answers to questions 1-9. Then write 150-word hard news story for questions 10-15. 

Email it to me with subject line Pivot Table quiz

Adapted from IRE exercise by Ron Nixon

You are a reporter at the Charleston (WV) Gazette. A source tells you that a number of school children in the Charleston area were being taken on field trips that had no education value. You want to find out if it were true. So you gather paper records of the field trips and put them into a spreadsheet. They are in moviebus1.xls The file contains a list of schools, field trips, dates and other information for each school in Kanahaw County, West Virginia.

In this exercise, you’ll make three Pivot Tables from this information to find out where kids went on most of their fieldtrips, which school took the most trips, and how much was spent. Then you will write a 150 story using the information.


Review Intro to Pivot Tables

Open the Excel file called moviebus1.xls

In order to use the pivot table, we have to tell Excel what data we want to use. Click on the blank space above the number “1” and to the left of the letter “A”. This should highlight the entire worksheet.

Select Data>Pivot Tables from top menu

Click OK create a wizard to create a Pivot Table on a New Worksheet.

You should see a the black Pivot Table Builder.

To make the following Pivot Tables and answer the questions, you will drag the variables you want into the Row Labels and Values.

Pivot Table 1: Make a pivot table that summarizes the type of trips taken. (Tip: Use Trip as BOTH your Row Label and Values)

Questions 1.2.3. What are the top three most frequent kinds of trips?

Pivot Table 2: Make a pivot table that shows schools and the number of trips. (Tip: Use School and Trips)

Questions: 4-6

4. How many trips were taken total?

5. Which school took the most trips?

6. How many trips did that school take?

Pivot Table 3: Make a pivot table that shows how much each school spent on trips. (Tip: Use School and Cost and click on “i” next to cost to find SUM)

Questions: 7-8

7. How much was spent on trips in total?

8. Which school spent the most on trips?

9. How much did the school spend in total?

Story Questions 10-15. Using the information in your three Pivot Tables, write the first 150 words of a hard news story about it. Summarize the basic 5 Ws. Incorporate the most important facts. Make it compelling.

This entry was posted in Quiz. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s