Excel Pivot Tables
In addition to recreating the tables in this tutorials, complete the following:
Create a PivotTable which can show whether the distribution [by count] of orders for fruit vs vegetable products differs across the 7 sales regions.
Modify your table using “Show Values as” so that you can meaningfully compare the split between categories across regions which have different subtotals.
Recode the “Category Recode” column in the spreadsheet to a 0/1 “dummy” coding, choosing “fruit” as the “1” category. Relabel the column so it is titled “Fruit”.
Create a PivotTable which calculates total orders [by count] in each region, and then uses this “dummy” variable with the Sum and Average functions [for three total entries in “Values”] to calculate the number [sum] and percentage [average] in each region that were for fruit products. [This is similar to the example we did of showing % public vs private schools, and the %s should look identical to your PivotTable in A above.]
Create a PivotTable which calculates the average sales per order for each product category (fruit vs vegetable) across the 7 sales regions. Also answer (to the best of your ability with the limited information) whether there could be anything to explain the differences in the $ sales (or order count) between categories across the 7 regions–is this necessarily indicative of a regional preference?
What lurking variables or confounders could there be?
Attach your completed Excel file as your submission. It should have a worksheet for each of the 4 tutorial sections and the additional questions from me above.
