Question

PRICE ($K)	SIZE	LOT	style	LOCATION
297.4	341	1702.4	Bungalow	Golf
193.3	199	757.62	Other	Golf
198.2	179	780.2	Other	Neither
160.6	94	539	Bungalow	Golf
168.5	221	774.18	Other	Golf
250.3	189	933	Other	Golf
195.0	165	906.84	Other	Neither
200.0	114	717.6	Other	Neither
232.2	211	1104.9	Other	Golf
178.6	133	1066.53	Other	Golf
233.6	209	1017.72	Other	Neither
162.1	139	664.3	Other	Neither
136.5	105	689	Other	Golf
183.4	175	755.55	Bungalow	Golf
157.8	178	555	Other	Neither
179.4	158	945.72	Other	Golf
157.8	163	609.55	Other	Golf
173.9	130	1002.84	Other	Neither
148.5	125	602.8	Bungalow	Golf
172.5	158	574.6	Other	Golf
169.2	130	770	Bungalow	Neither
164.5	134	735	Bungalow	Neither
219.9	229	1178.88	Other	Ravine
235.0	207	917.9	Other	Ravine
218.1	235	820.75	Other	Ravine
135.1	122	768.6	Bungalow	Neither
129.8	110	580.8	Bungalow	Neither
177.3	195	559.45	Other	Golf
183.0	136	747.63	Other	Neither
121.8	97	610	Bungalow	Neither
167.5	189	888.21	Other	Neither
236.7	200	985.5	Bungalow	Golf
120.7	103	561	Bungalow	Golf
122.6	110	554.8	Bungalow	Neither
122.6	114	556.32	Bungalow	Neither
300.7	214	2119.14	Bungalow	Golf
230.7	174	1003	Bungalow	Neither
114.5	114	554.8	Bungalow	Neither
145.8	178	566.5	Other	Neither
216.9	186	804	Bungalow	Golf
203.0	212	788.4	Other	Neither
266.6	223	2674.24	Bungalow	Ravine
161.1	165	688.08	Other	Neither
197.3	238	562.4	Other	Golf
139.3	116	582.56	Bungalow	Neither
158.3	172	685.56	Other	Ravine
238.2	214	686.28	Other	Ravine
149.4	159	569.5	Other	Neither
134.1	164	649.44	Other	Neither
175.3	190	1499	Bungalow	Neither
121.9	126	554.8	Bungalow	Neither
196.9	222	872.49	Other	Neither
173.2	172	779.58	Other	Neither
259.7	153	1213.8	Bungalow	Ravine
214.5	195	735.68	Other	Neither
218.4	233	1155	Other	Neither
222.3	188	867.24	Other	Neither
409.8	262	1038.18	Bungalow	Golf
229.7	224	758.41	Other	Neither
187.1	162	798	Other	Neither
190.2	198	584.5	Other	Neither
112.1	102	605.8	Bungalow	Neither
129.9	135.6	856	Bungalow	Neither
197.6	200.2	1115	Other	Neither
143.1	162.5	900	Bungalow	Neither
149.5	161.1	863	Other	Neither
207.5	203.6	1128	Other	Neither
139.7	150	735	Other	Neither
171.6	153	751.4	Bungalow	Neither
121.3	107	559.5	Bungalow	Neither
185.1	187.1	930.6	Other	Neither
155.3	133.3	1066.5	Bungalow	Neither
121.0	132.1	555	Bungalow	Neither
335.5	209.5	1627.5	Bungalow	Golf
197.4	204.4	813	Other	Neither
178.9	122.5	1022	Bungalow	Golf
274.6	216	2174	Other	Golf
143.1	163.7	556.9	Other	Neither
194.7	169	1109.6	Bungalow	Neither
108.8	112.7	622.7	Bungalow	Neither
132.3	124	564.2	Other	Neither
168.8	207	792.4	Other	Neither
190.9	119.4	900	Other	Neither
125.8	149.3	529.3	Bungalow	Neither
375.5	189.5	1860.5	Bungalow	Ravine
141.7	195	554.8	Other	Neither
157.1	184	777.5	Other	Neither
158.4	209	724.7	Other	Neither
243.7	299.8	908.5	Other	Neither
150.3	192.2	576.5	Other	Neither
182.8	204.8	966.2	Other	Neither
166.3	130.2	733.6	Other	Neither
154.9	188.1	768.6	Other	Neither
168.1	159	816.6	Bungalow	Neither
157.1	168	693.5	Other	Neither
183.9	133.5	1298.1	Other	Neither
156.5	178	803.8	Other	Neither
152.6	197	657	Other	Ravine
149.9	186	784.1	Other	Neither
136.3	114	717.6	Bungalow	Neither
140.6	178.9	777.5	Other	Neither
200.0	274.8	817.9	Other	Neither
144.4	168.5	680.6	Other	Neither
203.8	214	964.1	Bungalow	Neither
520.4	446	2620	Bungalow	Ravine
155.2	130.6	603.9	Bungalow	Neither
210.0	224.4	905.2	Other	Neither
172.2	151.1	802.8	Bungalow	Neither
155.6	167.6	1622.6	Other	Neither
346.9	269.4	1348.9	Other	Ravine
201.5	239.5	664.3	Other	Neither
177.1	199	830	Other	Neither
311.6	234	1359	Bungalow	Golf
237.7	220	936	Bungalow	Neither
201.4	193	726	Other	Neither
191.6	193	674	Other	Neither
193.9	176	785	Other	Neither
150.8	172	775	Other	Neither
559.1	430	1744	Bungalow	Golf
189.4	201	1115	Other	Neither
173.6	171	669	Other	Neither
199.4	167	873	Other	Golf
150.9	160.9	621	Other	Ravine
190.5	170	1115	Other	Ravine
142.6	135.4	601	Bungalow	Neither
120.9	105	603	Bungalow	Neither
208.7	221	988	Other	Ravine
179.4	177	725	Bungalow	Neither
121.4	122.7	572	Bungalow	Neither
183.8	179	1263	Other	Neither
138.0	184	769	Bungalow	Neither
195.9	253	872	Other	Neither
208.8	156	851	Bungalow	Ravine
175.7	220	1204	Other	Neither
152.9	128	709	Bungalow	Neither
164.6	169	769	Other	Neither
235.4	232	1061	Other	Neither
113.4	119.5	559	Bungalow	Neither
188.1	210	871	Other	Neither
139.6	122.7	572	Bungalow	Neither
147.9	139	925	Bungalow	Neither
416.1	315	1997	Other	Ravine
103.8	129	557	Bungalow	Neither
142.2	142	572	Bungalow	Ravine
152.9	121	684	Bungalow	Ravine
193.2	153	806	Bungalow	Neither
270.3	219	1862	Bungalow	Golf
154.1	181	872	Other	Neither
138.5	185	891	Other	Ravine
193.8	159	858	Bungalow	Neither
185.3	186	748	Other	Neither
201.3	207	976	Other	Ravine
162.2	105	737	Other	Ravine
260.7	191	1195	Other	Neither
184.5	244	919	Other	Golf
223.8	159	1350	Bungalow	Neither
111.2	151	753	Other	Neither
128.9	115	743	Other	Ravine
126.5	155	643	Bungalow	Ravine
141.5	119	761	Bungalow	Ravine

(1) Using the Data on the HousePriceData worksheet, create a pivot table to find the average house price in the “Neither” location. (To two decimal places) the average house price (in $K) is ______.

(2) Using the Data on the HousePriceData worksheet, create a pivot table to find the number of “Other” style houses in the “Ravine” location. The number of houses is ______.

(3) Using the Data on the HousePriceData worksheet, create a pivot table to find the percent of houses that are “Bungalow” style AND in the “Golf” location. (To zero decimal places) _______% of all the houses are “Bungalow” style AND in the “Golf location.

(4) Using the Data on the HousePriceData worksheet, create a pivot table to find the maximum size of an “Other” style house that’s in the “Golf” location. (To two decimal places) the maximum house size is _______.

(5) Using the Data on the HousePriceData worksheet, create a pivot table to find the standard deviation of size of an “Other” style house that’s in the “Ravine” location. (To two decimal places) the standarddeviation ofhouse size is _______.

Answer 1

Select the table and choose insert-->pivot table

get the following sheet

a) Now drag the location to row labels and price to  

click on the sum values-->Value Field settings and select the summarize the value by as "Average"

press OK and get the following

ans: the average house price in the “Neither” location is ($K) 168.25

2) the number of “Other” style houses in the “Ravine” location.

Drag field style to Row labels and style (again) to

make sure that sum values says: "count of style"

drag the field location to Column labels

get the following

ans: the number of “Other” style houses in the “Ravine” location is 15

3) the percent of houses that are “Bungalow” style AND in the “Golf” location

Drag field style to Row labels and style (again) to

make sure that sum values says: "count of style"

click on the count of style-->Value Field settings and pick the tab show values as and choose % of grand total

drag the field location to Column labels

ans: 9% of all the houses are “Bungalow” style AND in the “Golf location.

4) the maximum size of an “Other” style house that’s in the “Golf” location.

Drag field style to Row labels and size to

click on the sum of size-->Value Field settings and choose Max to summarize the value field by

drag the field location to Column labels

ans: the maximum size of an “Other” style house that’s in the “Golf” location is 244.00

5) the standard deviation of size of an “Other” style house that’s in the “Ravine” location

Drag field style to Row labels and size to

click on the sum of size-->Value Field settings and choose StdDev as the option to summarize (since this is a sample data we will calculate the sample standard deviation)

drag the field location to Column labels

ans: the standard deviation of size of an “Other” style house that’s in the “Ravine” location is 53.81