Question

This worksheet contains data on average annual low temperature and average annual snowfall for select cities. (Note: both of the following tables you are asked to create are more appropriate, given the small number of observations per state, had I created another variable describing the region--NE, SE, MW, Central, and West, for example.) 15) Create frequency distribution summarizing the number of cities included in the sample from each state. You are to use the Pivot Table tool to accomplish this task and insert it into the current worksheet with the top left corner of the Pivot Table in Cell E11. Hint: In the PivotTable fields menu, State should be included in the Rows category and in the Values category (use count) 16) Create a new Pivot Table with the top left corner of the Pivot Table in Cell J11 in this worksheet. Suppose we want to know the Average of "Average Low Temp" and Average of "Average Snowfall" by state. We want this information in a single Pivot Table. Small Hint: the Pivot Table will end up with 3 columns. I'm not sure of the correct way to format/set up the pivot table and what data to include.

Answer 1

Since I do not have the actual worksheet you are referring to, I am going to be working with dummy data and explain the process of setting up the correct pivot table to you through screenshots, along with briefly summarising the process based on my understanding of your problem. It would be great if you can provide the screenshots of the excel worksheet so I can guide you even better.

Dummy Data:

PART 15

1. Go to Insert-->Pivot table-->Chosse the table-->Choose the below picture settings. [Existing Worksheet-->$E$1 location]

2. Make the row labels and the column labels as shown in the picture below:

3. You now have the frequency distribution summarizing the number of cities included in each sample from each region.

PART 16

4. Now again go to insert -->Pivot table--> refer the below picture:

5. Please refer to the picture below to obtain the desired output:-

Additional Information: To obtain the Average of "Average Low Temp" you just click on "Average Low Temp" under Values and then Value Field settings should be modified as follows:

Please let me know in case you are still unclear.