Can anyone guide to perform the taks given in the attachment.

Office 2016 - myitlab:grader - Instructions Excel Project EX16_XL_CH05_GRADER_ML1_HW - Mountain View Realty 1.1 Project Description: Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Points Possible Instructions 1 Open exploring_e05_grader_h2_RealEstate.xlsx and save it as exploring_e05_grader_h2_RealEstate_LastFirst. 0.000 2 In cell G2 in the Sales Subtotals sheet, insert a formula to calculate the selling price percentage of the asking price, format it with Percent Style with one decimal place, and then copy the formula down the column. 5.000 3 In cell J2, enter a formula to calculate the number of days between the listing date and sale date. Copy the formula down the column. 4 Sort the list by City in alphabetical order, then by Selling Agent in alphabetical order, and finally by Listing Date in chronological order (oldest to newest). 5 Use the Subtotal feature to calculate the average selling price, percentage of asking price, and days on market by city. Ensure that Summary below data is selected, and then click OK. Group the data using the Auto Outline feature. Collapse the outline to hide the listing and sale dates. Click the outline symbol to display the grand average and city average rows only. 5.000 9.000 15.000 6 Format the average days on market as numbers with zero decimal places. Set a print area for the range C1:J88. 3.000 7 Go to cell C101, read the questions, and type the correct answers in the respective highlighted cells in the range G102:G106. Apply Accounting Number Format with zero decimal places to cell G102. 5.000 8 Click the Sales Data worksheet and create a blank PivotTable on a new worksheet. Name the new worksheet PivotTable. Name the PivotTable Average City Prices. 9 Place the City field in rows, the Selling Agent field in columns, and the Asking Price and Selling Price fields as values. Display averages rather than sums with Accounting Number Format with zero decimal places. Pivot the data by placing the City field below the Values field in the Columns area and moving the Selling Agent field to the Rows area. 10 11 Add a filter to cell B3 to display only Alpine and Cedar Hills. 1 9.000 2.000 Go back to the Sales Data worksheet. You realize that a selling price is incorrect. Change the selling price for Number 40 from $140,000 to $1,400,000. Refresh the PivotTable. Updated: 02/16/2017 10.000 3.000 Current_Instruction.docx Office 2016 - myitlab:grader - Instructions Step Excel Project Points Possible Instructions 12 Change the widths of columns A, B, C, D, and E to 11. Change the widths of columns F and G to 14. Wrap text and center horizontally data in cells B4, D4, F4, and G4. Apply the Bottom Border to the range B4:E4. Change the label in cell A5 to Agent. Change the height of row 4 to 40. 13 Display the Sales Data worksheet. Create a recommended PivotTable using the Sum of Selling Price by City thumbnail. Change the name of the new PivotTable worksheet to Selling Price. 14 Change the value to display averages not sums. Apply the Accounting Number Format with 0 decimal places to the values. Apply Light Blue, Pivot Style Medium 2 to the PivotTable. 10.000 5.000 6.000 Note, depending upon the version of Office being used, the style name may be Pivot Style Medium 2. 15 Create a clustered column PivotChart from the PivotTable on the Selling Price worksheet. Move the chart to a chart sheet named Sales Chart. 5.000 16 Change the chart title to Average Selling Price by City and apply Dark Blue font color. Remove the legend. Apply Blue, Accent 1 fill color to the data series. 6.000 17 Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of all worksheets. Adjust the scaling if needed. 2.000 18 Save the workbook. Ensure that the worksheets are named correctly and in the following order: Sales Subtotals, PivotTable, Sales Chart, Selling Price, Sales Data. Close the workbook and exit Excel. Submit the file as directed. 0.000 Total Points Updated: 02/16/2017 2 100.000 Current_Instruction.docx Number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 Address 8687 Kenwood Road 11 West Oak Circle 314 Timp View Drive 614 West Cedar Drive 750 South Apple Way 7 Kingston Court 618 West Cedar Drive 321 North Choctaw 1370 Pinellas Road 1971 Glenview Road 15 West Oak Circle 575 South Choctaw 10995 SW 88 Court 143 North Mountain View 29 East Oak Circle 8030 Steeplechase Drive 2006 Cutwater Court 9876 South Sunset Avenue 321 West Walnut Grove 4081 Lybyer Avenue 1900 Glenview Road 9000 South Sunset Avenue 8432 South Sunset Avenue 831 South Weber 714 Timp View Lane 240 East Jefferson Way 224 Rockaway Street 260 East Jefferson Way 290 East Jefferson Way 8307 S Indian River Drive 123 Ivy Lane 9408 Forest Hills Circle 245 Ivy Lane 11971 SW 269 Terrace 1857 Pine Drive 3461 East Lindon Way 16235 Orange Boulevard 614 Lincoln Drive 2448 Woodacres Road 401 Pinecone Circle 421 Ivy Lane 765 East Sheridan Lane 61 East Walnut Grove 1414 N Sheridan Road 487 Blue Skies Drive 10700 Lake Shore Lane 73 East Oak Street 81 Island Drive South 3412 Kilmer Street 605 Reservoir Drive 77 East Oak Street 400 Ivy Lane 1 Southampton Place 3418 North Sunset Lane 1892 North 250 West City Cedar Hills American Fork American Fork Lehi American Fork Alpine Lehi American Fork Alpine Lehi American Fork Alpine Lehi American Fork American Fork Alpine Alpine Eagle Mountain American Fork Alpine Lehi Eagle Mountain Eagle Mountain American Fork American Fork Eagle Mountain Alpine Eagle Mountain Eagle Mountain Lehi Eagle Mountain Cedar Hills Eagle Mountain Cedar Hills Cedar Hills American Fork Cedar Hills Eagle Mountain Lehi Alpine Eagle Mountain Eagle Mountain American Fork Alpine American Fork Cedar Hills Eagle Mountain Alpine Lehi Alpine Eagle Mountain Eagle Mountain Lehi Eagle Mountain American Fork Selling Agent Asking Price Selling Price Hernandez Carey Goodrich Lugo Goodrich Carey Lugo Minkus Lugo Minkus Hernandez Goodrich Goodrich Carey Merkin Merkin Minkus Hernandez Goodrich Minkus Hernandez Lugo Minkus Lugo Goodrich Lugo Pijuan Hernandez Pijuan Hernandez Pijuan Reuter Goodrich Carey Hernandez Reuter Lugo Reuter Hernandez Goodrich Pijuan Minkus Goodrich Pijuan Goodrich Carey Merkin Bethune Carey Reuter Bethune Bethune Goodrich Bethune Carey $ 725,000 $ $ 350,000 $ $ 418,000 $ $ 215,800 $ $ 385,900 $ $ 500,000 $ $ 300,000 $ $ 565,000 $ $ 219,000 $ $ 165,000 $ $ 325,000 $ $ 750,250 $ $ 110,000 $ $ 314,250 $ $ 555,000 $ $ 450,000 $ $ 345,000 $ $ 300,000 $ $ 425,000 $ $ 325,000 $ $ 325,000 $ $ 325,000 $ $ 285,750 $ $ 425,815 $ $ 250,000 $ $ 515,000 $ $ 400,000 $ $ 450,000 $ $ 310,000 $ $ 250,000 $ $ 375,500 $ $ 185,500 $ $ 395,000 $ $ 410,000 $ $ 560,700 $ $ 450,000 $ $ 395,000 $ $ 475,000 $ $ 450,000 $ $ 1,500,120 $ $ 500,000 $ $ 460,750 $ $ 375,000 $ $ 1,250,000 $ $ 365,750 $ $ 650,000 $ $ 325,000 $ $ 147,800 $ $ 180,000 $ $ 310,000 $ $ 345,000 $ $ 375,000 $ $ 215,000 $ $ 450,000 $ $ 315,250 $ 645,250 340,000 400,000 200,000 385,900 465,000 290,000 535,000 215,000 156,750 320,000 700,000 106,000 304,000 500,000 382,500 339,999 300,000 415,000 308,750 302,250 320,000 300,000 400,000 232,000 485,750 375,000 400,000 300,000 255,000 375,500 179,000 375,000 397,700 550,000 400,000 380,000 425,250 382,500 1,400,000 425,000 435,500 376,000 1,225,000 355,000 598,000 325,000 150,000 175,000 291,400 335,000 330,000 195,000 400,000 300,000 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 1629 NW 43rd Street 3400 North Sunset Lane 876 South California Way 21 Compass Road 34 West Oakley Drive 30 Kent Road 140 East 3rd Street 160 West 5th Street 240 West 5th Street 520 E Spring Street 678 Mountain Circle 3490 North Sunset Lane 4916 Rock Spring Road 8923 Harris Drive 876 West Hadley Way 1132 SW 52nd Street 416 East Oak 10 Vestal Drive 425 East Oak 876 West Holiday 103 Jasper Drive 516 East Oak 123 Oak Avenue 600 Ivy Lane 144 Oak Avenue 1600 Reeves Street Cedar Hills Eagle Mountain American Fork Cedar Hills American Fork Lehi Eagle Mountain Eagle Mountain Eagle Mountain Alpine American Fork Eagle Mountain Cedar Hills Alpine American Fork Cedar Hills Eagle Mountain Cedar Hills Eagle Mountain Alpine Lehi Eagle Mountain American Fork Eagle Mountain American Fork Cedar Hills Carey Merkin Hen Merkin Carey Bethune Hen Carey Reuter Minkus Goodrich Minkus Lugo Minkus Hernandez Hernandez Merkin Carey Bethune Goodrich Bethune Pijuan Carey Pijuan Goodrich Reuter $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 475,000 400,000 316,000 289,900 345,000 259,900 399,000 410,000 285,750 189,900 335,000 250,000 275,000 589,000 345,670 254,500 300,000 555,000 275,900 475,000 165,900 299,999 400,000 339,999 380,500 245,900 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 450,000 375,000 316,000 279,000 330,000 246,905 350,000 350,750 300,000 186,102 330,000 275,000 264,000 575,000 345,000 236,685 250,000 565,000 250,000 455,000 159,264 280,000 400,000 310,000 365,000 233,605 Answer these questions: What is the average selling price in Alpine? Which city has the highest average % of asking price? Which city has the least number of average days on market? What is the average days on market for all cities? Which city had the lowest average selling price? % of Asking Listing Date Sale Date Price 4/1/2018 6/16/2018 4/1/2018 5/15/2018 4/1/2018 6/1/2018 4/1/2018 5/25/2018 4/1/2018 4/30/2018 4/3/2018 6/10/2018 4/3/2018 6/1/2018 4/6/2018 9/30/2018 4/8/2018 6/2/2018 4/12/2018 6/12/2018 4/12/2018 5/30/2018 4/12/2018 11/1/2018 4/12/2018 7/3/2018 4/13/2018 8/5/2018 4/13/2018 10/15/2018 4/15/2018 6/30/2018 4/18/2018 6/23/2018 4/18/2018 4/30/2018 4/18/2018 6/30/2018 4/19/2018 7/2/2018 4/28/2018 6/1/2018 4/28/2018 5/28/2018 4/28/2018 6/3/2018 4/28/2018 9/15/2018 4/29/2018 9/1/2018 4/29/2018 8/25/2018 4/30/2018 6/19/2018 4/30/2018 10/1/2018 4/30/2018 7/1/2018 5/1/2018 6/18/2018 5/1/2018 5/5/2018 5/1/2018 6/6/2018 5/1/2018 7/16/2018 5/1/2018 6/26/2018 5/1/2018 5/30/2018 5/2/2018 9/30/2018 5/5/2018 7/16/2018 5/5/2018 9/23/2018 5/5/2018 6/16/2018 5/5/2018 9/30/2018 5/5/2018 11/15/2018 5/5/2018 11/1/2018 5/6/2018 5/15/2018 5/12/2018 6/6/2018 5/13/2018 8/15/2018 5/15/2018 6/9/2018 5/18/2018 5/31/2018 5/22/2018 6/16/2018 5/23/2018 7/15/2018 5/22/2018 6/29/2018 5/23/2018 6/23/2018 5/26/2018 8/30/2018 5/28/2018 6/18/2018 5/28/2018 8/15/2018 5/28/2018 8/1/2018 Days on Market 5/31/2018 8/19/2018 5/31/2018 7/15/2018 5/31/2018 6/30/2018 5/31/2018 6/1/2018 6/1/2018 8/15/2018 6/2/2018 9/2/2018 6/2/2018 11/30/2018 6/3/2018 10/15/2018 6/5/2018 7/1/2018 6/6/2018 7/26/2018 6/10/2018 9/15/2018 6/12/2018 6/25/2018 6/13/2018 7/21/2018 6/18/2018 7/8/2018 6/20/2018 9/30/2018 6/23/2018 9/21/2018 6/23/2018 11/2/2018 6/24/2018 6/30/2018 6/24/2018 8/15/2018 6/24/2018 9/18/2018 6/29/2018 9/6/2018 6/29/2018 9/30/2018 6/30/2018 7/15/2018 6/30/2018 10/1/2018 6/30/2018 9/1/2018 6/30/2018 8/16/2018 Number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Address City 8687 Kenwood Road Cedar Hills 11 West Oak Circle American Fork 314 Timp View Drive American Fork 614 West Cedar Drive Lehi 750 South Apple Way American Fork 7 Kingston Court Alpine 618 West Cedar Drive Lehi 321 North Choctaw American Fork 1370 Pinellas Road Alpine 1971 Glenview Road Lehi 15 West Oak Circle American Fork 575 South Choctaw Alpine 10995 SW 88 Court Lehi 143 North Mountain View American Fork 29 East Oak Circle American Fork 8030 Steeplechase Drive Alpine 2006 Cutwater Court Alpine 9876 South Sunset AvenueEagle Mountain 321 West Walnut Grove American Fork 4081 Lybyer Avenue Alpine 1900 Glenview Road Lehi 9000 South Sunset AvenueEagle Mountain 8432 South Sunset AvenueEagle Mountain 831 South Weber American Fork 714 Timp View Lane American Fork 240 East Jefferson Way Eagle Mountain 224 Rockaway Street Alpine 260 East Jefferson Way Eagle Mountain 290 East Jefferson Way Eagle Mountain 8307 S Indian River Drive Lehi 123 Ivy Lane Eagle Mountain 9408 Forest Hills Circle Cedar Hills 245 Ivy Lane Eagle Mountain 11971 SW 269 Terrace Cedar Hills 1857 Pine Drive Cedar Hills 3461 East Lindon Way American Fork 16235 Orange Boulevard Cedar Hills 614 Lincoln Drive Eagle Mountain 2448 Woodacres Road Lehi 401 Pinecone Circle Alpine 421 Ivy Lane Eagle Mountain Selling Agent Hernandez Carey Goodrich Lugo Goodrich Carey Lugo Minkus Lugo Minkus Hernandez Goodrich Goodrich Carey Merkin Merkin Minkus Hernandez Goodrich Minkus Hernandez Lugo Minkus Lugo Goodrich Lugo Pijuan Hernandez Pijuan Hernandez Pijuan Reuter Goodrich Carey Hernandez Reuter Lugo Reuter Hernandez Goodrich Pijuan Asking Price $ 725,000 $ 350,000 $ 418,000 $ 215,800 $ 385,900 $ 500,000 $ 300,000 $ 565,000 $ 219,000 $ 165,000 $ 325,000 $ 750,250 $ 110,000 $ 314,250 $ 555,000 $ 450,000 $ 345,000 $ 300,000 $ 425,000 $ 325,000 $ 325,000 $ 325,000 $ 285,750 $ 425,815 $ 250,000 $ 515,000 $ 400,000 $ 450,000 $ 310,000 $ 250,000 $ 375,500 $ 185,500 $ 395,000 $ 410,000 $ 560,700 $ 450,000 $ 395,000 $ 475,000 $ 450,000 $ 1,500,120 $ 500,000 Selling Price Listing Date Sale Date $ 645,250 4/1/2018 6/16/2018 $ 340,000 4/1/2018 5/15/2018 $ 400,000 4/1/2018 6/1/2018 $ 200,000 4/1/2018 5/25/2018 $ 385,900 4/1/2018 4/30/2018 $ 465,000 4/3/2018 6/10/2018 $ 290,000 4/3/2018 6/1/2018 $ 535,000 4/6/2018 9/30/2018 $ 215,000 4/8/2018 6/2/2018 $ 156,750 4/12/2018 6/12/2018 $ 320,000 4/12/2018 5/30/2018 $ 700,000 4/12/2018 11/1/2018 $ 106,000 4/12/2018 7/3/2018 $ 304,000 4/13/2018 8/5/2018 $ 500,000 4/13/2018 10/15/2018 $ 382,500 4/15/2018 6/30/2018 $ 339,999 4/18/2018 6/23/2018 $ 300,000 4/18/2018 4/30/2018 $ 415,000 4/18/2018 6/30/2018 $ 308,750 4/19/2018 7/2/2018 $ 302,250 4/28/2018 6/1/2018 $ 320,000 4/28/2018 5/28/2018 $ 300,000 4/28/2018 6/3/2018 $ 400,000 4/28/2018 9/15/2018 $ 232,000 4/29/2018 9/1/2018 $ 485,750 4/29/2018 8/25/2018 $ 375,000 4/30/2018 6/19/2018 $ 400,000 4/30/2018 10/1/2018 $ 300,000 4/30/2018 7/1/2018 $ 255,000 5/1/2018 6/18/2018 $ 375,500 5/1/2018 5/5/2018 $ 179,000 5/1/2018 6/6/2018 $ 375,000 5/1/2018 7/16/2018 $ 397,700 5/1/2018 6/26/2018 $ 550,000 5/1/2018 5/30/2018 $ 400,000 5/2/2018 9/30/2018 $ 380,000 5/5/2018 7/16/2018 $ 425,250 5/5/2018 9/23/2018 $ 382,500 5/5/2018 6/16/2018 $ 140,000 5/5/2018 9/30/2018 $ 425,000 5/5/2018 11/15/2018 Number 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 Address 765 East Sheridan Lane 61 East Walnut Grove 1414 N Sheridan Road 487 Blue Skies Drive 10700 Lake Shore Lane 73 East Oak Street 81 Island Drive South 3412 Kilmer Street 605 Reservoir Drive 77 East Oak Street 400 Ivy Lane 1 Southampton Place 3418 North Sunset Lane 1892 North 250 West 1629 NW 43rd Street 3400 North Sunset Lane 876 South California Way 21 Compass Road 34 West Oakley Drive 30 Kent Road 140 East 3rd Street 160 West 5th Street 240 West 5th Street 520 E Spring Street 678 Mountain Circle 3490 North Sunset Lane 4916 Rock Spring Road 8923 Harris Drive 876 West Hadley Way 1132 SW 52nd Street 416 East Oak 10 Vestal Drive 425 East Oak 876 West Holiday 103 Jasper Drive 516 East Oak 123 Oak Avenue 600 Ivy Lane 144 Oak Avenue 1600 Reeves Street City Eagle Mountain American Fork Alpine American Fork Cedar Hills Eagle Mountain Alpine Lehi Alpine Eagle Mountain Eagle Mountain Lehi Eagle Mountain American Fork Cedar Hills Eagle Mountain American Fork Cedar Hills American Fork Lehi Eagle Mountain Eagle Mountain Eagle Mountain Alpine American Fork Eagle Mountain Cedar Hills Alpine American Fork Cedar Hills Eagle Mountain Cedar Hills Eagle Mountain Alpine Lehi Eagle Mountain American Fork Eagle Mountain American Fork Cedar Hills Selling Agent Minkus Goodrich Pijuan Goodrich Carey Merkin Bethune Carey Reuter Bethune Bethune Goodrich Bethune Carey Carey Merkin Hen Merkin Carey Bethune Hen Carey Reuter Minkus Goodrich Minkus Lugo Minkus Hernandez Hernandez Merkin Carey Bethune Goodrich Bethune Pijuan Carey Pijuan Goodrich Reuter Asking Price $ 460,750 $ 375,000 $ 1,250,000 $ 365,750 $ 650,000 $ 325,000 $ 147,800 $ 180,000 $ 310,000 $ 345,000 $ 375,000 $ 215,000 $ 450,000 $ 315,250 $ 475,000 $ 400,000 $ 316,000 $ 289,900 $ 345,000 $ 259,900 $ 399,000 $ 410,000 $ 285,750 $ 189,900 $ 335,000 $ 250,000 $ 275,000 $ 589,000 $ 345,670 $ 254,500 $ 300,000 $ 555,000 $ 275,900 $ 475,000 $ 165,900 $ 299,999 $ 400,000 $ 339,999 $ 380,500 $ 245,900 Selling Price Listing Date Sale Date $ 435,500 5/5/2018 11/1/2018 $ 376,000 5/6/2018 5/15/2018 $ 1,225,000 5/12/2018 6/6/2018 $ 355,000 5/13/2018 8/15/2018 $ 598,000 5/15/2018 6/9/2018 $ 325,000 5/18/2018 5/31/2018 $ 150,000 5/22/2018 6/16/2018 $ 175,000 5/23/2018 7/15/2018 $ 291,400 5/22/2018 6/29/2018 $ 335,000 5/23/2018 6/23/2018 $ 330,000 5/26/2018 8/30/2018 $ 195,000 5/28/2018 6/18/2018 $ 400,000 5/28/2018 8/15/2018 $ 300,000 5/28/2018 8/1/2018 $ 450,000 5/31/2018 8/19/2018 $ 375,000 5/31/2018 7/15/2018 $ 316,000 5/31/2018 6/30/2018 $ 279,000 5/31/2018 6/1/2018 $ 330,000 6/1/2018 8/15/2018 $ 246,905 6/2/2018 9/2/2018 $ 350,000 6/2/2018 11/30/2018 $ 350,750 6/3/2018 10/15/2018 $ 300,000 6/5/2018 7/1/2018 $ 186,102 6/6/2018 7/26/2018 $ 330,000 6/10/2018 9/15/2018 $ 275,000 6/12/2018 6/25/2018 $ 264,000 6/13/2018 7/21/2018 $ 575,000 6/18/2018 7/8/2018 $ 345,000 6/20/2018 9/30/2018 $ 236,685 6/23/2018 9/21/2018 $ 250,000 6/23/2018 11/2/2018 $ 565,000 6/24/2018 6/30/2018 $ 250,000 6/24/2018 8/15/2018 $ 455,000 6/24/2018 9/18/2018 $ 159,264 6/29/2018 9/6/2018 $ 280,000 6/29/2018 9/30/2018 $ 400,000 6/30/2018 7/15/2018 $ 310,000 6/30/2018 10/1/2018 $ 365,000 6/30/2018 9/1/2018 $ 233,605 6/30/2018 8/16/2018