Use the excel sheet as a reference. 

question one: 


Let's calculate the Closing Cost (in column M) for ALL properties. It is determined based on the Style of the property and is summarized in the look-up table in R24:T26. Note that on th elook-up table, the first column is the style of the property, the second column is the county code, and the third column is the corresponding closing cost. The county code is not used for the calculation. Select ALL of the correct answers below. You will have to get all the answers right in order to receive credit for this question.

a. The correct formula for property #101 is:

=VLOOKUP(M2,$R$24:$T$26,3,TRUE)

b. The correct formula for property #101 is:

=VLOOKUP(E2,$R$24:$T$26,3,FALSE)


c. The correct formula for property #101 is:

=VLOOKUP(M2,$R$24:$T$26,2,FALSE)

d. 
The closing cost for property #125 is $400.

e. 
The correct formula for property #101 is:

=VLOOKUP(E2,$R$24:$T$26,2,TRUE)

ID	Asking Price	Square feet	Age	Style	Bedroom	Bath	Date Listed	Date Sold	Sales Price			
101	 $87,400 	1236	3	Condo	2	1	22/11/2012	14/04/2013	 $77,750 			
102	 $110,900 	1740	4	Victorian	2	1.5	23/08/2012	20/06/2013	 $105,250 			
103	 $95,000 	1715	4	Ranch	2	1.5	18/05/2013	22/05/2013	 $95,000 			
104	 $87,000 	1273	4	Condo	2	1	30/09/2012	12/05/2013	 $81,750 			
105	 $73,900 	970	4	Victorian	1	0.5	07/02/2013	24/04/2013	 $63,500 			
106	 $77,000 	900	5	Victorian	1	0.5	21/06/2012	14/05/2013	 $67,750 			
107	 $133,000 	1850	5	Ranch	2	1.5	02/03/2013	16/05/2013	 $115,500 			
108	 $116,000 	1720	5	Ranch	2	1.5	18/03/2013	23/04/2013	 $99,750 			
109	 $102,000 	1606	5	Ranch	2	1.5	05/08/2012	29/05/2013	 $103,000 			
110	 $94,000 	1305	5	Victorian	2	1.5	20/06/2012	25/05/2013	 $88,250 			
111	 $112,900 	1700	6	Condo	2	1.5	04/02/2013	08/04/2013	 $115,000 			
112	 $105,000 	1620	6	Condo	2	1.5	27/06/2012	03/05/2013	 $106,000 			
113	 $104,500 	1630	6	Condo	2	1.5	22/07/2012	02/04/2013	 $98,000 			
114	 $103,000 	1540	6	Ranch	2	1.5	24/03/2013	12/06/2013	 $90,500 			
115	 $87,200 	1229	6	Ranch	2	1	23/01/2013	26/06/2013	 $87,000 			
116	 $97,500 	1500	7	Ranch	2	1.5	28/06/2012	16/04/2013	 $82,750 			
117	 $100,000 	1535	7	Victorian	2	1.5	09/05/2012	28/04/2013	 $91,000 			
118	 $92,000 	1415	7	Ranch	2	1.5	06/07/2012	24/04/2013	 $91,000 			
119	 $86,900 	1165	7	Victorian	2	1	10/08/2012	15/05/2013	 $88,500 			
120	 $76,600 	1200	7	Condo	2	1	07/05/2012	01/05/2013	 $68,000 			
121	 $105,000 	1920	8	Ranch	2	1.5	27/04/2013	14/05/2013	 $95,500 			
122	 $94,500 	1580	9	Victorian	2	1.5	05/04/2013	15/06/2013	 $95,250 			
123	 $97,500 	1739	13	Victorian	2	1.5	10/05/2013	21/06/2013	 $84,750 			
124	 $70,000 	1505	14	Condo	2	1.5	15/03/2013	30/05/2013	 $70,500 			
125	 $60,000 	1198	14	Ranch	2	1	08/03/2013	09/05/2013	 $51,000 			
126	 $158,000 	2563	14	Victorian	3	2.5	19/11/2012	11/06/2013	 $159,500 			
127	 $58,000 	1051	15	Ranch	2	1	05/12/2012	17/05/2013	 $58,000 			
128	 $169,500 	3750	19	Victorian	4	2.5	14/03/2013	30/05/2013	 $165,000 			
129	 $78,000 	1080	21	Victorian	2	1	09/01/2013	02/04/2013	 $68,500 			
130	 $54,000 	1142	21	Victorian	2	1	11/10/2012	05/05/2013	 $51,750 			
131	 $107,000 	1464	22	Victorian	2	1.5	22/12/2012	25/05/2013	 $107,000 			
132	 $210,000 	2116	25	Victorian	3	2	01/09/2012	20/06/2013	 $195,250 			
133	 $66,000 	1159	25	Ranch	2	1	13/09/2012	13/06/2013	 $62,500 			
134	 $72,500 	1280	38	Victorian	2	1	01/03/2013	04/05/2013	 $70,250 			
135	 $93,900 	1428	40	Victorian	2	1.5	03/10/2012	23/05/2013	 $82,500 			
136	 $82,000 	1375	40	Victorian	2	1.5	17/05/2012	06/05/2013	 $82,750 			
137	 $62,000 	1480	40	Ranch	2	1.5	11/09/2012	20/05/2013	 $59,500 			
138	 $184,400 	2250	40	Condo	3	2	04/11/2012	21/05/2013	 $182,500 			
139	 $69,900 	1400	45	Ranch	2	1.5	10/03/2013	10/04/2013	 $60,750 			
140	 $156,000 	1920	1	Ranch	2	1.5	29/12/2012	28/04/2013	 $134,000 			
141	 $144,900 	1710	1	Victorian	2	1.5	16/04/2013	02/06/2013	 $143,250 			
142	 $180,000 	2774	2	Victorian	3	2.5	10/12/2012	05/04/2013	 $165,500 			
143	 $215,000 	2921	3	Victorian	3	2.5	17/11/2012	06/05/2013	 $215,000 			
144	 $75,500 	1275	3	Condo	2	1	10/10/2012	01/04/2013	 $77,000 			
145	 $199,900 	2580	4	Ranch	3	2.5	30/06/2012	18/04/2013	 $187,750 			
146	 $190,000 	2580	4	Victorian	3	2.5	02/09/2012	22/04/2013	 $171,000 			
147	 $137,500 	1837	4	Victorian	2	1.5	16/04/2012	07/04/2013	 $137,500 			
148	 $215,000 	2848	4	Ranch	3	2.5	25/12/2012	30/05/2013	 $193,500 			
149	 $75,000 	1338	4	Ranch	2	1.5	08/04/2013	15/05/2013	 $76,500 			
150	 $75,900 	997	4	Ranch	1	0.5	13/06/2012	02/06/2013	 $75,000 			
151	 $87,000 	1280	5	Victorian	2	1	23/01/2013	27/06/2013	 $87,000 			
152	 $85,500 	1900	5	Ranch	2	1.5	04/07/2012	01/06/2013	 $81,000 			
153	 $215,000 	2664	6	Victorian	3	2.5	20/09/2012	13/06/2013	 $212,750 			
154	 $87,500 	1173	6	Victorian	2	1	29/08/2012	27/05/2013	 $87,500 			
155	 $81,000 	1365	6	Ranch	2	1.5	29/06/2012	23/06/2013	 $81,750 			
156	 $75,000 	1030	6	Ranch	2	1	19/11/2012	28/05/2013	 $69,750 			
157	 $88,900 	1549	7	Condo	2	1.5	04/06/2012	04/05/2013	 $77,250 			
158	 $80,500 	1258	7	Victorian	2	1	16/04/2013	27/05/2013	 $74,750 			
159	 $127,000 	1880	8	Ranch	2	1.5	27/07/2012	29/04/2013	 $127,000 			
160	 $208,000 	2600	10	Victorian	3	2.5	04/08/2012	29/04/2013	 $178,750 			
161	 $145,000 	2150	10	Victorian	3	2	02/11/2012	01/06/2013	 $146,250 			
162	 $89,000 	1746	10	Condo	2	1.5	04/02/2013	13/04/2013	 $77,250 			
163	 $61,900 	837	10	Condo	1	0.5	05/02/2013	20/06/2013	 $53,000 			
164	 $130,000 	2000	11	Victorian	2	2	15/05/2013	25/05/2013	 $117,000 			
165	 $83,500 	1560	11	Ranch	2	1.5	13/08/2012	13/05/2013	 $73,250 			
166	 $99,500 	1725	12	Condo	2	1.5	25/02/2013	12/04/2013	 $98,500 			
167	 $205,000 	2650	13	Victorian	3	2.5	09/01/2013	21/06/2013	 $178,250 			
168	 $97,500 	1430	13	Victorian	2	1.5	19/05/2012	05/04/2013	 $99,250 			
169	 $92,500 	1050	13	Ranch	2	1	04/05/2013	19/06/2013	 $78,500 			
170	 $105,000 	1680	13	Ranch	2	1.5	26/04/2012	06/04/2013	 $101,750 			
171	 $67,000 	1350	13	Condo	2	1.5	23/09/2012	06/05/2013	 $68,250 			
172	 $123,500 	1894	14	Ranch	2	1.5	27/07/2012	15/05/2013	 $123,500 			
173	 $97,500 	1360	14	Condo	2	1.5	25/05/2012	01/04/2013	 $97,500 			
174	 $92,200 	1326	14	Victorian	2	1.5	20/04/2013	20/06/2013	 $81,000 			
175	 $123,900 	1706	14	Ranch	2	1.5	10/05/2013	13/05/2013	 $110,250 			
176	 $120,000 	1948	14	Condo	2	1.5	01/10/2012	03/04/2013	 $120,000 			
177	 $79,900 	1314	14	Condo	2	1.5	23/02/2013	02/04/2013	 $70,250 			
178	 $125,000 	2150	15	Ranch	3	2	06/01/2013	25/05/2013	 $123,750 			
179	 $111,000 	1630	15	Victorian	2	1.5	13/07/2012	25/04/2013	 $99,750 			
180	 $99,500 	1500	15	Victorian	2	1.5	11/05/2013	29/05/2013	 $99,500 			
181	 $70,000 	1215	15	Ranch	2	1	28/08/2012	15/04/2013	 $67,750 			
182	 $73,000 	1027	15	Victorian	2	1	18/11/2012	16/06/2013	 $70,750 			
183	 $69,000 	1348	15	Victorian	2	1.5	06/02/2013	04/06/2013	 $70,250 			
184	 $115,500 	1767	16	Condo	2	1.5	28/07/2012	31/05/2013	 $103,750 			
185	 $90,000 	1400	16	Victorian	2	1.5	10/09/2012	06/05/2013	 $89,000 			
186	 $86,000 	1385	16	Victorian	2	1.5	28/05/2012	08/04/2013	 $75,500 			
187	 $112,500 	1710	16	Victorian	2	1.5	05/10/2012	19/04/2013	 $114,750 			
188	 $113,900 	1680	17	Ranch	2	1.5	09/06/2012	03/06/2013	 $105,750 			
189	 $96,000 	1573	17	Victorian	2	1.5	20/06/2012	08/06/2013	 $89,250 			
190	 $125,000 	2180	17	Ranch	3	2	02/02/2013	07/04/2013	 $121,250 			
191	 $117,000 	1928	18	Victorian	2	1.5	06/11/2012	18/04/2013	 $112,250 			
192	 $159,900 	2440	19	Victorian	3	2	26/02/2013	26/05/2013	 $163,000 			
193	 $72,900 	1007	19	Ranch	2	1	04/01/2013	24/04/2013	 $64,750 			
194	 $110,000 	1657	20	Ranch	2	1.5	16/06/2012	29/05/2013	 $100,000 			
195	 $95,500 	1565	20	Ranch	2	1.5	08/11/2012	10/06/2013	 $93,500 			
196	 $93,400 	1543	20	Ranch	2	1.5	07/10/2012	16/04/2013	 $83,000 			
197	 $67,000 	1181	21	Ranch	2	1	13/02/2013	07/05/2013	 $61,500 			
198	 $89,900 	1464	22	Condo	2	1.5	14/05/2012	03/04/2013	 $90,750 			
199	 $71,000 	1083	22	Ranch	2	1	15/10/2012	18/05/2013	 $61,000 			
200	 $118,000 	1830	23	Victorian	2	1.5	10/06/2012	01/04/2013	 $114,250 			
201	 $87,600 	1156	23	Condo	2	1	14/07/2012	04/05/2013	 $76,000 			
202	 $135,000 	2253	23	Ranch	3	2	25/04/2013	29/04/2013	 $116,000 			
203	 $73,100 	1299	24	Condo	2	1	23/12/2012	30/05/2013	 $70,000 			
204	 $77,300 	1320	24	Ranch	2	1.5	20/12/2012	04/04/2013	 $75,750 			
205	 $125,000 	2277	25	Ranch	3	2	09/08/2012	07/05/2013	 $121,250 			
206	 $129,900 	2743	25	Victorian	3	2.5	13/09/2012	07/06/2013	 $116,750 			
207	 $108,000 	2200	26	Condo	3	2	20/07/2012	28/04/2013	 $100,250 			
208	 $169,500 	2931	28	Victorian	3	2.5	01/07/2012	08/05/2013	 $169,500 			
209	 $155,300 	2200	28	Victorian	3	2	21/02/2013	20/06/2013	 $141,250 			
210	 $102,000 	1713	30	Condo	2	1.5	09/02/2013	07/05/2013	 $98,750 			
211	 $72,500 	1140	30	Ranch	2	1	05/01/2013	27/06/2013	 $66,500 			
212	 $104,900 	1900	34	Victorian	2	1.5	11/08/2012	11/04/2013	 $97,500 			
213	 $72,000 	1050	40	Ranch	2	1	23/07/2012	17/04/2013	 $65,500 			
214	 $85,000 	1190	41	Ranch	2	1	02/10/2012	20/05/2013	 $73,750 			
215	 $74,900 	1733	43	Victorian	2	1.5	01/12/2012	04/05/2013	 $66,500 			
216	 $72,000 	1121	46	Ranch	2	1	02/09/2012	20/04/2013	 $66,750 			
217	 $102,000 	1478	53	Ranch	2	1.5	21/12/2012	17/04/2013	 $91,750 			

ID	Asking Price	Square feet	Age	Style	Bedroom	Bath	Date Listed	Date Sold	Sales Price	Warranty purchase	Warranty cost	Closing cost	Tax Rate	Condo Fee						
101	 $87,400 	1236	3	Condo	2	1	22/11/2012	14/04/2013	 $77,750 	Y										
102	 $110,900 	1740	4	Victorian	2	1.5	23/08/2012	20/06/2013	 $105,250 	Y										
103	 $95,000 	1715	4	Ranch	2	1.5	18/05/2013	22/05/2013	 $95,000 	Y										
104	 $87,000 	1273	4	Condo	2	1	30/09/2012	12/05/2013	 $81,750 	N										
105	 $73,900 	970	4	Victorian	1	0.5	07/02/2013	24/04/2013	 $63,500 	N										
106	 $77,000 	900	5	Victorian	1	0.5	21/06/2012	14/05/2013	 $67,750 	N							Condo fee	$150 		
107	 $133,000 	1850	5	Ranch	2	1.5	02/03/2013	16/05/2013	 $115,500 	Y								only for condos with 2 or more bedrooms		
108	 $116,000 	1720	5	Ranch	2	1.5	18/03/2013	23/04/2013	 $99,750 	Y										
109	 $102,000 	1606	5	Ranch	2	1.5	05/08/2012	29/05/2013	 $103,000 	Y							Warranty Cost	$200 	Age < 5	
110	 $94,000 	1305	5	Victorian	2	1.5	20/06/2012	25/05/2013	 $88,250 	N								$300 	Age >=5	
111	 $112,900 	1700	6	Condo	2	1.5	04/02/2013	08/04/2013	 $115,000 	N								only pay if purchased		
112	 $105,000 	1620	6	Condo	2	1.5	27/06/2012	03/05/2013	 $106,000 	Y										
113	 $104,500 	1630	6	Condo	2	1.5	22/07/2012	02/04/2013	 $98,000 	Y							Tax Rate Table			
114	 $103,000 	1540	6	Ranch	2	1.5	24/03/2013	12/06/2013	 $90,500 	Y							Square feet	County Code	Tax Rate	
115	 $87,200 	1229	6	Ranch	2	1	23/01/2013	26/06/2013	 $87,000 	N							0	10	0.15%	
116	 $97,500 	1500	7	Ranch	2	1.5	28/06/2012	16/04/2013	 $82,750 	N							1000	11	0.21%	
117	 $100,000 	1535	7	Victorian	2	1.5	09/05/2012	28/04/2013	 $91,000 	Y							1500	12	0.22%	
118	 $92,000 	1415	7	Ranch	2	1.5	06/07/2012	24/04/2013	 $91,000 	Y							2000	13	0.24%	
119	 $86,900 	1165	7	Victorian	2	1	10/08/2012	15/05/2013	 $88,500 	Y							3000	14	0.25%	
120	 $76,600 	1200	7	Condo	2	1	07/05/2012	01/05/2013	 $68,000 	Y										
121	 $105,000 	1920	8	Ranch	2	1.5	27/04/2013	14/05/2013	 $95,500 	Y							Closing Cost Table			
122	 $94,500 	1580	9	Victorian	2	1.5	05/04/2013	15/06/2013	 $95,250 	Y							Style	County Code	Cost	
123	 $97,500 	1739	13	Victorian	2	1.5	10/05/2013	21/06/2013	 $84,750 	Y							Condo	10	200	
124	 $70,000 	1505	14	Condo	2	1.5	15/03/2013	30/05/2013	 $70,500 	N							Victorian	11	250	
125	 $60,000 	1198	14	Ranch	2	1	08/03/2013	09/05/2013	 $51,000 	Y							Ranch	12	400	
126	 $158,000 	2563	14	Victorian	3	2.5	19/11/2012	11/06/2013	 $159,500 	N										
127	 $58,000 	1051	15	Ranch	2	1	05/12/2012	17/05/2013	 $58,000 	N							Average Sales Price for:			
128	 $169,500 	3750	19	Victorian	4	2.5	14/03/2013	30/05/2013	 $165,000 	N							All 2 bedroom properties			
129	 $78,000 	1080	21	Victorian	2	1	09/01/2013	02/04/2013	 $68,500 	Y							All Victorian properties			
130	 $54,000 	1142	21	Victorian	2	1	11/10/2012	05/05/2013	 $51,750 	N							Properties with 1.5 bathrooms			
131	 $107,000 	1464	22	Victorian	2	1.5	22/12/2012	25/05/2013	 $107,000 	N										
132	 $210,000 	2116	25	Victorian	3	2	01/09/2012	20/06/2013	 $195,250 	N										
133	 $66,000 	1159	25	Ranch	2	1	13/09/2012	13/06/2013	 $62,500 	Y										
134	 $72,500 	1280	38	Victorian	2	1	01/03/2013	04/05/2013	 $70,250 	Y										
135	 $93,900 	1428	40	Victorian	2	1.5	03/10/2012	23/05/2013	 $82,500 	Y										
136	 $82,000 	1375	40	Victorian	2	1.5	17/05/2012	06/05/2013	 $82,750 	N										
137	 $62,000 	1480	40	Ranch	2	1.5	11/09/2012	20/05/2013	 $59,500 	N										
138	 $184,400 	2250	40	Condo	3	2	04/11/2012	21/05/2013	 $182,500 	Y										
139	 $69,900 	1400	45	Ranch	2	1.5	10/03/2013	10/04/2013	 $60,750 	N										
140	 $156,000 	1920	1	Ranch	2	1.5	29/12/2012	28/04/2013	 $134,000 	Y										
141	 $144,900 	1710	1	Victorian	2	1.5	16/04/2013	02/06/2013	 $143,250 	N										
142	 $180,000 	2774	2	Victorian	3	2.5	10/12/2012	05/04/2013	 $165,500 	Y										
143	 $215,000 	2921	3	Victorian	3	2.5	17/11/2012	06/05/2013	 $215,000 	N										
144	 $75,500 	1275	3	Condo	2	1	10/10/2012	01/04/2013	 $77,000 	Y										
145	 $199,900 	2580	4	Ranch	3	2.5	30/06/2012	18/04/2013	 $187,750 	Y										
146	 $190,000 	2580	4	Victorian	3	2.5	02/09/2012	22/04/2013	 $171,000 	Y										
147	 $137,500 	1837	4	Victorian	2	1.5	16/04/2012	07/04/2013	 $137,500 	N										
148	 $215,000 	2848	4	Ranch	3	2.5	25/12/2012	30/05/2013	 $193,500 	N										
149	 $75,000 	1338	4	Ranch	2	1.5	08/04/2013	15/05/2013	 $76,500 	Y										
150	 $75,900 	997	4	Ranch	1	0.5	13/06/2012	02/06/2013	 $75,000 	Y										
151	 $87,000 	1280	5	Victorian	2	1	23/01/2013	27/06/2013	 $87,000 	N										
152	 $85,500 	1900	5	Ranch	2	1.5	04/07/2012	01/06/2013	 $81,000 	N										
153	 $215,000 	2664	6	Victorian	3	2.5	20/09/2012	13/06/2013	 $212,750 	N										
154	 $87,500 	1173	6	Victorian	2	1	29/08/2012	27/05/2013	 $87,500 	Y										
155	 $81,000 	1365	6	Ranch	2	1.5	29/06/2012	23/06/2013	 $81,750 	Y										
156	 $75,000 	1030	6	Ranch	2	1	19/11/2012	28/05/2013	 $69,750 	N										
157	 $88,900 	1549	7	Condo	2	1.5	04/06/2012	04/05/2013	 $77,250 	Y										
158	 $80,500 	1258	7	Victorian	2	1	16/04/2013	27/05/2013	 $74,750 	N										
159	 $127,000 	1880	8	Ranch	2	1.5	27/07/2012	29/04/2013	 $127,000 	Y										
160	 $208,000 	2600	10	Victorian	3	2.5	04/08/2012	29/04/2013	 $178,750 	N										
161	 $145,000 	2150	10	Victorian	3	2	02/11/2012	01/06/2013	 $146,250 	Y										
162	 $89,000 	1746	10	Condo	2	1.5	04/02/2013	13/04/2013	 $77,250 	N										
163	 $61,900 	837	10	Condo	1	0.5	05/02/2013	20/06/2013	 $53,000 	N										
164	 $130,000 	2000	11	Victorian	2	2	15/05/2013	25/05/2013	 $117,000 	N										
165	 $83,500 	1560	11	Ranch	2	1.5	13/08/2012	13/05/2013	 $73,250 	Y										
166	 $99,500 	1725	12	Condo	2	1.5	25/02/2013	12/04/2013	 $98,500 	Y										
167	 $205,000 	2650	13	Victorian	3	2.5	09/01/2013	21/06/2013	 $178,250 	Y										
168	 $97,500 	1430	13	Victorian	2	1.5	19/05/2012	05/04/2013	 $99,250 	Y										
169	 $92,500 	1050	13	Ranch	2	1	04/05/2013	19/06/2013	 $78,500 	Y										
170	 $105,000 	1680	13	Ranch	2	1.5	26/04/2012	06/04/2013	 $101,750 	N										
171	 $67,000 	1350	13	Condo	2	1.5	23/09/2012	06/05/2013	 $68,250 	N										
172	 $123,500 	1894	14	Ranch	2	1.5	27/07/2012	15/05/2013	 $123,500 	N										
173	 $97,500 	1360	14	Condo	2	1.5	25/05/2012	01/04/2013	 $97,500 	N										
174	 $92,200 	1326	14	Victorian	2	1.5	20/04/2013	20/06/2013	 $81,000 	Y										
175	 $123,900 	1706	14	Ranch	2	1.5	10/05/2013	13/05/2013	 $110,250 	Y										
176	 $120,000 	1948	14	Condo	2	1.5	01/10/2012	03/04/2013	 $120,000 	N										
177	 $79,900 	1314	14	Condo	2	1.5	23/02/2013	02/04/2013	 $70,250 	N										
178	 $125,000 	2150	15	Ranch	3	2	06/01/2013	25/05/2013	 $123,750 	Y										
179	 $111,000 	1630	15	Victorian	2	1.5	13/07/2012	25/04/2013	 $99,750 	N										
180	 $99,500 	1500	15	Victorian	2	1.5	11/05/2013	29/05/2013	 $99,500 	Y										
181	 $70,000 	1215	15	Ranch	2	1	28/08/2012	15/04/2013	 $67,750 	Y										
182	 $73,000 	1027	15	Victorian	2	1	18/11/2012	16/06/2013	 $70,750 	N										
183	 $69,000 	1348	15	Victorian	2	1.5	06/02/2013	04/06/2013	 $70,250 	N										
184	 $115,500 	1767	16	Condo	2	1.5	28/07/2012	31/05/2013	 $103,750 	Y										
185	 $90,000 	1400	16	Victorian	2	1.5	10/09/2012	06/05/2013	 $89,000 	Y										
186	 $86,000 	1385	16	Victorian	2	1.5	28/05/2012	08/04/2013	 $75,500 	N										
187	 $112,500 	1710	16	Victorian	2	1.5	05/10/2012	19/04/2013	 $114,750 	N										
188	 $113,900 	1680	17	Ranch	2	1.5	09/06/2012	03/06/2013	 $105,750 	N										
189	 $96,000 	1573	17	Victorian	2	1.5	20/06/2012	08/06/2013	 $89,250 	Y										
190	 $125,000 	2180	17	Ranch	3	2	02/02/2013	07/04/2013	 $121,250 	Y										
191	 $117,000 	1928	18	Victorian	2	1.5	06/11/2012	18/04/2013	 $112,250 	N										
192	 $159,900 	2440	19	Victorian	3	2	26/02/2013	26/05/2013	 $163,000 	N										
193	 $72,900 	1007	19	Ranch	2	1	04/01/2013	24/04/2013	 $64,750 	Y										
194	 $110,000 	1657	20	Ranch	2	1.5	16/06/2012	29/05/2013	 $100,000 	Y										
195	 $95,500 	1565	20	Ranch	2	1.5	08/11/2012	10/06/2013	 $93,500 	Y										
196	 $93,400 	1543	20	Ranch	2	1.5	07/10/2012	16/04/2013	 $83,000 	Y										
197	 $67,000 	1181	21	Ranch	2	1	13/02/2013	07/05/2013	 $61,500 	N										
198	 $89,900 	1464	22	Condo	2	1.5	14/05/2012	03/04/2013	 $90,750 	N										
199	 $71,000 	1083	22	Ranch	2	1	15/10/2012	18/05/2013	 $61,000 	Y										
200	 $118,000 	1830	23	Victorian	2	1.5	10/06/2012	01/04/2013	 $114,250 	N										
201	 $87,600 	1156	23	Condo	2	1	14/07/2012	04/05/2013	 $76,000 	Y										
202	 $135,000 	2253	23	Ranch	3	2	25/04/2013	29/04/2013	 $116,000 	N										
203	 $73,100 	1299	24	Condo	2	1	23/12/2012	30/05/2013	 $70,000 	Y										
204	 $77,300 	1320	24	Ranch	2	1.5	20/12/2012	04/04/2013	 $75,750 	Y										
205	 $125,000 	2277	25	Ranch	3	2	09/08/2012	07/05/2013	 $121,250 	Y										
206	 $129,900 	2743	25	Victorian	3	2.5	13/09/2012	07/06/2013	 $116,750 	N										
207	 $108,000 	2200	26	Condo	3	2	20/07/2012	28/04/2013	 $100,250 	N										
208	 $169,500 	2931	28	Victorian	3	2.5	01/07/2012	08/05/2013	 $169,500 	Y										
209	 $155,300 	2200	28	Victorian	3	2	21/02/2013	20/06/2013	 $141,250 	Y										
210	 $102,000 	1713	30	Condo	2	1.5	09/02/2013	07/05/2013	 $98,750 	Y										
211	 $72,500 	1140	30	Ranch	2	1	05/01/2013	27/06/2013	 $66,500 	Y										
212	 $104,900 	1900	34	Victorian	2	1.5	11/08/2012	11/04/2013	 $97,500 	N										
213	 $72,000 	1050	40	Ranch	2	1	23/07/2012	17/04/2013	 $65,500 	N										
214	 $85,000 	1190	41	Ranch	2	1	02/10/2012	20/05/2013	 $73,750 	N										
215	 $74,900 	1733	43	Victorian	2	1.5	01/12/2012	04/05/2013	 $66,500 	Y										
216	 $72,000 	1121	46	Ranch	2	1	02/09/2012	20/04/2013	 $66,750 	Y										
217	 $102,000 	1478	53	Ranch	2	1.5	21/12/2012	17/04/2013	 $91,750 	Y										
218	 $130,000 	1525	22	Ranch	3	2	01/05/2013			Y										
Average sales price:									 $100,462 											
																				
Question number two:



Go to the bottom of the list. The average of all the Sales Price is listed in cell J120. Notice that Propery #218 is listed but not sold yet. How much does Property #218 need to be sold at (to be shown in cell J119) in order for the average sales price to become $100,800? Only enter whole number in the answer box; no dollar sign, no comma, no decimal point.



answer: __________
																				
question three: 



Add a Total Row to the table. Display only the properties with 1 bathroom. What is the Average Sales Price of all the 1 bathroom properties? Write your answer in whle number; no dollar sign, no comma, no decimal.

answer: _____
																				
question four : 



Create a PivotTable to calculate summary statistics. Use the following filed as labels: Style, Bedroom, and Date Sold. And use the Sales Price as the value field. Answer the following questions:

1. What is the number of Condo properties with 2 bedrooms that were sold in May 2013? answer: ______
2. What is the average sales price for all the Ranch properties with 2 bedrooms sold in April 2013? answer: _______
Make sure you enter whole number only; no dollar sign, no comma, no decimal. You will need to get both answers right to receive credit for this question.



question five: 




Conditional format the column Sales Price to highlight the bottom 7% of the price in green. How many properties have a green highlighted cell?



11


10


9


8


7

 
croani posted a reply
Jun 19, 2014 at 8:43pm
question six: 





Calculate Condo Fee in column O. Only those properties that are condos with 2 or more bedrooms have to pay a condo fee of $150, listed in cell S7. Enter a formula to calculate condo fees for ALL the properties. What is the correct formula (use property #101 as an example)?




a. =IF(E2="Condo",150,IF(F2>=2,0,0)


b. =IF(AND(F2>=2,E2="Condo"),S7,0)


c. =IF(OR(E2="Condo",F2>=2),S7,0)


d. =IF(AND(E2="Condo",F2>=2),$S$7,0)


e. =IF(OR(F2<2,E2="Condo"),S$7,0)
question seven: 



To calculate the warranty cost, the following criteria are used (as in R10:T12):

If a warranty is purchased (that is, the value in Warranty purchase is Y), then the warranty cost is determined by the age of the property. The cost is $200 when the age of the property is less than 5 years old, and $300 when it is greater than or equal to 5 years old. When a warranty is not purchased (that is, the value in Warranty purchase is N), the warranty cost is 0.

Use a nested IF formula to calculate the warranty cost in column L for All the properties (remember to autofill). Choose ALL the correct answers below. You have to pick all the correct answers to receive credit for this question.



1. The correct formula (using propery #121 as an example) is:

=IF(D22<5,200,IF(K22>=5,300,IF(K22="N",0)))


2. The correct formula (using propery #121 as an example) is:

=IF(K22="N",0,IF(D22>=5,300,200))


3. The correct formula (using propery #121 as an example) is:

=IF(D22="Y",300,IF(D22<5,200,0))


4. The warranty cost for property #119 is $300.


5. The warranty cost for property #158 is $200.

question eight: 



Sort the table in the following order:

Square Feet, largest to smallest
Style, A to Z
Age, smallest to largest
Date Sold, newset to oldest
Make sure you use the exact order with the correct criteria for each. What is the ID number (in column A) for the first property on the sorted list?



a. 128


b. 140


c. 153


d. 217


e. 193

question nine: 



Enter a formula in the cell T29 to calculate the average Sales Price for all the properties with 2 bedrooms. Note that the number of Bedrooms is recorded in column F, and the Sales Price is in column J. Write the result shown in T29 in the answer box in whole number; no dollar sign, no comma, no decimal point.



answer: _____