a. (5 points) In column D for all list prices, apply a conditional formatting so that...
Fantastic news! We've Found the answer you've been seeking!
Question:
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
Expert Answer:
Related Book For
Posted Date:
Students also viewed these general management questions
-
Define the elements of Strategic Reasoning. For each provide a specific example of what can be missed if the element is NOT used.
-
In this project, you will update a workbook to display bank transactions as a PivotTable. You will filter the PivotTable, format the values, display the values as calculations, and create a...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Why is an objects internal data usually hidden from outside code?
-
a. How will the Fed's monetary policy change based on the report? b. How will the likely change in the Fed's monetary policy affect Carson's future performance? Could it affect Carson's plans for...
-
Show how you would use a Suzuki reaction to synthesize the following biaryl compound. As starting materials you may use the two indicated compounds, plus any additional reagents you need. Make OCH3...
-
What pleadings the defendant may file in response to the complaint?
-
In Born together-Reared apart: the Landmark Minnesota twin study (2012), Nancy Segal discusses the efforts of research psychologists at the University of Minnesota to understand similarities and...
-
Linda Company began making cosmetics in April 2021 using a single-step process. Linda incurred P21,000 for materials and P24,320 for conversion during the month. There were 14,000 pints of cosmetics...
-
Goldie and Kurt want advice from your financial advising firm. They have provided the following information. They graduated from university four years ago and they have good jobs, but neither of them...
-
Consider a 2x2x2 Heckscher-Ohlin model with two countries: Greece and Italy; two goods: olives and cheese; two inputs: labour and capital. Suppose that Greece is capital abundant, and you are given...
-
Herb and Alice are married and file a joint return. Herb is 7 5 years old and Alice turns 7 3 in February 2 0 2 4. Neither are bilna. Both Herb and Alice are retired. Herb works part time as a...
-
Jolie Foster Care Homes Incorporated shows the following data: Year 20X1 Net Income $ 155,000 Total Assets $ 2,390,000 20X2 191,000 2,700,000 20X3 208,000 2,730,000 20X4 192,000 2,470,000...
-
The current maintenance policy being adopted for a complex transfer machine in continuous operation is that inspections are made once every 4 weeks. Any potential defects that are detected during...
-
Stewiacke Ltd. is currently considering a project with a four-year life that it believes may return the company to profitability. Stewiacke recently did a market survey at a cost of $100,000. The...
-
Grove Media plans to acquire production equipment for $800,000 that will be depreciated for tax purposes as follows: year 1, $320,000; year 2, $180,000; and in each of years 3 through 5, $100,000 per...
-
On December 31, 2020, Pepper Company paid 3,000 to purchase a 90-day call option for 500,000 Thailand Baht. The option's purpose is to protect an exposed liability of 500,000 Baht relating to a...
-
1. As a general strategy, would you recommend that Carl take an aggressive approach to capacity expansion or more of a wait-and-see approach? 2. Should Carl go with the option for one facility that...
-
Why should companies estimate the environmental costs of a given job? Why have EPR (extended producer responsibility) laws come into existence?
-
Rocky River Fast Lube does oil changes on vehicles in 15 minutes or less. The variable cost associated with each oil change is $12 (oil, filter, and 15 minutes of employee time). The fixed costs of...
-
Persian Fast, an independently owned restaurant chain, specializes in Persian cuisine fast food. Following is a partial performance report that a Persian Fast manager has been working on using Excel....
-
The shareholders equity for Best in Show Cinemas L.td. (BSC) (adapted) at December 31. 2019 , appears as follows: On April 15, 2020, the market price of BSC common shares was \(\$ 17\) per share....
-
Identify the effects--both the direction and the dollar amount-of the following assumed transactions on the total shareholders' equity of a large corporation. Each transaction is independent. a....
-
Solartech Inc. had the following sharcholders' equity at January 31 (dollars in millions): Assume that on March 7, Solartech split its common shares 2 for 1. Prepare the shareholders' equity section...
Study smarter with the SolutionInn App