a. (5 points) In column D for all list prices, apply a conditional formatting so that...

Transcribed Image Text:

a. (5 points) In column D for all list prices, apply a conditional formatting so that the top 30% list prices will display with color Light Red Fill with Dark Red Text. b. (10 points) Use a combination of INDEX, MATCH, and MAX functions to find out the listing with highest sale price and display its Listing ID, Sq Ft, Address, and Listing price in cell C3 to C6. c. (5 points) In column O, use a text function to join City (column F) to the end of Address (column E); use space as delimitator. d. (5 points) In cell D28, we want to find the total sale price for houses that are built before year 2000. Hint: use SUMIF function. e. (5 points) In cell D29, we want to know the number of listings whose sale price is higher than list price. Hint: use COUNTIF function. f. (5 points) Create a PivotTable like this one: Beds (All) Sum of Sale Price City Agent ID 103 127 151 160 168 Grand Total Auburn Davis Elk Grove Lincoln Rocklin Roseville Sacramento Grand Total $340,000 $925,000 $238,000 $327,000 $1,830,000 $345,000 $1,045,000 $1,390,000 $890,000 $1,427,000 $237,000 $300,000 $315,000 $465,000 $1,327,900 $505,500 $265,900 $1,726,400 $645,000 $310,000 $1,197,000 $950,000 $1,895,500 $503,900 $555,900 $664,000 $1,935,000 $7,701,300 $228,900 $319,000 4 S 4 7 . C 30 TE 32 11 24 SP 36 SP 36 00 11 22 23 24 25 N 24 17 Led 2015-05-30 2018-06-04 2015-06-09 2015-06-21 2019-06-21 2019-07-14 2019-07-15 20190734 2019-09-23 2015-10-05 2015-10-08 2019-10-11 2019 1034 2015-10-17 2015-10-20 2019-10-23 2015-10-26 2019-10-29 2019-3101 2015-11-04 2015-11-07 C 160 127 101 148 148 ce for Houses built before year 2000 2ngs with sale price higher than list price 301 148 101: D List Price Addres $277.000 1096 Kinyane $297.000 272 Lant Leop 151 160 160 5239.600 1755 Copperede $329,000 124 Center Po $326 500 411 Marion Stree 151 1475.99917521 Onder 148 3525.000 1044(eStre $545.000 15802 Cent 327 $189.000 14313 Clev 101 1489.000 12222 South Street $315.0002330120 148 127 5418.000 7240 Witbrook Dive 101 $505.000 419 ariem Avenue 151 $317.000 1023 Evergreen Lan 153 $48.000 4750 Madison Street $499.000 1330 Street 5255,000 1420 M $335.000 2178 Wegwood $314.500 4336 Newland Heges Drive $385.000 131 Ala Drive $274,500 12355 F City Rockin ville Mackin Aubu Auburn incade Jurcoin Auburn eville Autrem Sacramente Grave Jow mante Grove Davis eramente Grove Dets Sacramente Grave G HA 1910 1184 1840 1901 2234 1948 3323 1456 1480 1950 0014 XPS5 1956 2790 2190 1700 1900 2000 1850 1990 1100 HE Medi 4 31 3 1 3 D C ) 3 1 1 J 1 ( 1 23 1 " 2 T F Bath } Garage Car Attached 21 Car Detached 22 Attached 22 2 Car Attached Brick 13 Aface ria 12 Mach Share 32 Detached frame 33 Detached hume Stare 2 CH Archer 37 Arcacher Jack frame Mare Ma 32 Car Deal frame Stare Ma PRINGFIE 22 PERINE PERIN POSE 22 Car Aache 32 Caracked Car Ac 22 Car Attached Car Amache Construction Frame Stone frame Brick K Brick frame L Year bil 1441 M 2001 1995 1999 1965 2015-09-15 $385.000 1984 2015-08-17 $240,000 1995 201-3001 1218.000 2001 2014-05-16 $745, 900 1995 2015.04.30 1217.000 2015-11-01 $319,000 2001 2019-12-01 $111.000 2000 2014-12-31 1411,000 $505,500 2013 1998 20200104 1140,000 2005 1990 N Sale Date 201906-22 5228.900 2014-07-30 $345.000 2018-30-13 $327,000 RECURISE 2000 2019-42-45 1400.000 2000 2030-01-24 1475.000 1992 2020-02-01 2001 38300241 2001 2020-02-23 2001 2030-04 06 2020-01-11 2022-05-25 $110.000 $445.000 $100.000 $100 000 $475,000 $445,000 O Fut Addr a. (5 points) In column D for all list prices, apply a conditional formatting so that the top 30% list prices will display with color Light Red Fill with Dark Red Text. b. (10 points) Use a combination of INDEX, MATCH, and MAX functions to find out the listing with highest sale price and display its Listing ID, Sq Ft, Address, and Listing price in cell C3 to C6. c. (5 points) In column O, use a text function to join City (column F) to the end of Address (column E); use space as delimitator. d. (5 points) In cell D28, we want to find the total sale price for houses that are built before year 2000. Hint: use SUMIF function. e. (5 points) In cell D29, we want to know the number of listings whose sale price is higher than list price. Hint: use COUNTIF function. f. (5 points) Create a PivotTable like this one: Beds (All) Sum of Sale Price City Agent ID 103 127 151 160 168 Grand Total Auburn Davis Elk Grove Lincoln Rocklin Roseville Sacramento Grand Total $340,000 $925,000 $238,000 $327,000 $1,830,000 $345,000 $1,045,000 $1,390,000 $890,000 $1,427,000 $237,000 $300,000 $315,000 $465,000 $1,327,900 $505,500 $265,900 $1,726,400 $645,000 $310,000 $1,197,000 $950,000 $1,895,500 $503,900 $555,900 $664,000 $1,935,000 $7,701,300 $228,900 $319,000 4 S 4 7 . C 30 TE 32 11 24 SP 36 SP 36 00 11 22 23 24 25 N 24 17 Led 2015-05-30 2018-06-04 2015-06-09 2015-06-21 2019-06-21 2019-07-14 2019-07-15 20190734 2019-09-23 2015-10-05 2015-10-08 2019-10-11 2019 1034 2015-10-17 2015-10-20 2019-10-23 2015-10-26 2019-10-29 2019-3101 2015-11-04 2015-11-07 C 160 127 101 148 148 ce for Houses built before year 2000 2ngs with sale price higher than list price 301 148 101: D List Price Addres $277.000 1096 Kinyane $297.000 272 Lant Leop 151 160 160 5239.600 1755 Copperede $329,000 124 Center Po $326 500 411 Marion Stree 151 1475.99917521 Onder 148 3525.000 1044(eStre $545.000 15802 Cent 327 $189.000 14313 Clev 101 1489.000 12222 South Street $315.0002330120 148 127 5418.000 7240 Witbrook Dive 101 $505.000 419 ariem Avenue 151 $317.000 1023 Evergreen Lan 153 $48.000 4750 Madison Street $499.000 1330 Street 5255,000 1420 M $335.000 2178 Wegwood $314.500 4336 Newland Heges Drive $385.000 131 Ala Drive $274,500 12355 F City Rockin ville Mackin Aubu Auburn incade Jurcoin Auburn eville Autrem Sacramente Grave Jow mante Grove Davis eramente Grove Dets Sacramente Grave G HA 1910 1184 1840 1901 2234 1948 3323 1456 1480 1950 0014 XPS5 1956 2790 2190 1700 1900 2000 1850 1990 1100 HE Medi 4 31 3 1 3 D C ) 3 1 1 J 1 ( 1 23 1 " 2 T F Bath } Garage Car Attached 21 Car Detached 22 Attached 22 2 Car Attached Brick 13 Aface ria 12 Mach Share 32 Detached frame 33 Detached hume Stare 2 CH Archer 37 Arcacher Jack frame Mare Ma 32 Car Deal frame Stare Ma PRINGFIE 22 PERINE PERIN POSE 22 Car Aache 32 Caracked Car Ac 22 Car Attached Car Amache Construction Frame Stone frame Brick K Brick frame L Year bil 1441 M 2001 1995 1999 1965 2015-09-15 $385.000 1984 2015-08-17 $240,000 1995 201-3001 1218.000 2001 2014-05-16 $745, 900 1995 2015.04.30 1217.000 2015-11-01 $319,000 2001 2019-12-01 $111.000 2000 2014-12-31 1411,000 $505,500 2013 1998 20200104 1140,000 2005 1990 N Sale Date 201906-22 5228.900 2014-07-30 $345.000 2018-30-13 $327,000 RECURISE 2000 2019-42-45 1400.000 2000 2030-01-24 1475.000 1992 2020-02-01 2001 38300241 2001 2020-02-23 2001 2030-04 06 2020-01-11 2022-05-25 $110.000 $445.000 $100.000 $100 000 $475,000 $445,000 O Fut Addr