Create a PivotTable which can show whether the distribution [by count] of orders for fruit vs vegetable products differs across the 7 sales regions.

Excel Pivot Tables

Review sections 1-4 of the “Excel Easy” PivotTable tutorial (Links to an external site.).

Use the data file below to follow along with the tutorial and replicate the PivotTables shown as close as you can. Work each section on a new worksheet.

Quant Check 6 Pivot Table Data.xlsx  Download Quant Check 6 Pivot Table Data.xlsx

The data contains information about produce sales for a given company to various regions in a given sales year. Each row is a single order that was shipped.

Direct links to the relevant required sections are given below:

Introduction to PivotTables (Links to an external site.)
Grouping PivotTable Items (Links to an external site.)
Multi-level PivotTables (Links to an external site.)
Frequency Distributions (Links to an external site.)

Section 4 demonstrates our preferred method for generating a histogram, and does not use the “Histogram” chart or the Data Analysis “Histogram” option.

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  to calculate the number [sum] and percentage [average] in each region that were for fruit products.

Create a PivotTable which calculates the average sales per order for each product category (fruit vs vegetable) across the 7 sales regions.

Also answer whether there could be anything to explain the differences in the $ sales between categories across the 7 regions is this necessarily indicative of a regional preference? What lurking variables or confounders could there be?

Last Completed Projects