Exploring the House Sales Data With a Pivot Table

• Have worked with a larger spreadsheet.
• Have explored a larger data set.
• have used pivot tables to explore data.
• have created a pivot chart.

For this exercise we will explore the data set further.

1. Either start with your data from the previous homework or download this workbook.
2. Begin by exploring a single dimension of the data, the relationship between average price and zip code.
   • Build a pivot table.
     • Rename this worksheet zip code
   • Drag price into the values area
     • Change the computation to be average
     • Change the format to be some financial format.
   • Drag the zip code field into the rows area.
   • Format the results.
     • Click on the row labels and select More Sort Options
       • Select Descending
       • Select the Average Price
       • This should show the zip codes with the highest average price first.
     • Select the value filter and select top 10
     • 
   • Build a pivot chart from this data.
     • 
   • 
3. The number of bedrooms and bathrooms in a house is important. Let's explore these characteristics in the data.
   • Build a new pivot table, rename this sheet Beds & Baths
   • We want to count the number of houses sold that match the criteria, so we can just use a count of any field for values. In this case, use the count of the id.
     • Drag the id field into the values area.
     • Change the computation to be count.
   • Drag Baths into the Rows area and Bed into the Columns area.
   • Using the Grand Totals drop down on the Pivot Table Design tab, remove both the row and column totals.
   • Is there any data that we should investigate?
     • I am a little concerned about the 33 bedroom house.
     • Double click in the cell that represents this house (in my case it was N11
     • This will open up a new worksheet with the data for this house.
     • It sure looks like a typo to me. I don't think there is sufficient space in an 1,600 sq ft house for 33 bedrooms.
     • Go back to the kc_house_data tab and change this 33 to be 3.
     • Back on the Beds & Baths tab, select refresh all in the connections work group. The 33 bath house should be removed.
     • Are there any other combinations that seem wrong? Explore these.
   • 
4. I don't understand what the Condition field means. Build a final pivot table to explore this.
   • Build a new pivot table, label the worksheet Condition
   • Place the view field in the rows.
   • Place the waterfront field in the columns area.
     • The lack of labels makes this hard to read so go back to the kc_house_data worksheet.
       • In the waterfront column, change 0 to be No Waterfront and 1 to be Waterfront (Find and replace)
       • Back in the Condition worksheet refresh.
     • In the values are place two items
       • Average of price, formatted as a money type
       • Count of id, formatted as an integer (no decimal places)
     • Remove the row and column totals.
     • Finally, build a filter to explore conditions.
       • Drag Condition into the filter area.
       • 
     • Using the condition filter, display only the houses sold in condition 5.
5. Save this workbook.
6. Submit the word document to the Houses Part 3 folder in the Assignment section of D2L for this class.