Closing Cost by admin | Apr 21, 2017 | Uncategorized | 0 comments 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: _____ Order now
