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)
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)
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.