Grader Instructions Excel 2016 Project EX16_XL_CH04_GRADER_CAP_AS - Mountain View Realty 1.6 Project Description: You work at Mountain View Realty. A coworker developed a spreadsheet cataloging houses listed and sold during the past several months. She included addresses, location, list price, selling price, listing date, and date sold You want to convert the data to a table You will manage the large worksheet, prepare the worksheet for printing, sort and filter the table, include calculations, and then format the table. Steps to Perform: Step Instructions Points Possible 1 2 0 5 6 2 3 4 5 2 6 5 7 Open the downloaded file exploring e04 grader a1.xlsx. Freeze the first row on the Sales Data worksheet Convert the data to a table and apply the Table Style Medium 17 Remove duplicate records. Insert a new field immediately to the right of the Selling Price field. Name the new field Percent of List Price Create a formula with structured references to calculate the percentage of the list price Format the field with Percent Style with one decimal place Insert a new field to the right of the Sale Date field. Name the new field Days on Market. Create a formula with structured references to calculate the number of days on the market. Apply the General number format to the values Add a total row to display the average percentage of list price and average number of days on market. Format the average number of days on market as a whole number Use an appropriate label for the total row. Sort the table by city in alphabetical order and add a second level to sort by days on market with the houses on the market the longest at the top within each city Adjust column widths so that all data is visible Wrap the column labels 8 2 I 9 6 10 5 11 7 12 5 contain viruses. Unless you need to edit it's safer to stay in Protected View. Enable Editing 14 6 15 2 16 Add a page break at Row 36 so that the city data does not spon between pages, and return to Normal view, if necessary Add a footer with your name on the left side, the sheet name code in the center, and the file name code on the night side Display the Faltered Data worksheet Filter the data to display the cities of Alpine, Cedar Hills, and Eagle Mountain Filter the data to display records for houses that were on the market 30 days or more Apply the conditional formatting 3 Arrows (Colored) icon set to the Days on Market values 5 17 5 18 7 Created On 07/05/2019 1 EX16 XL CHO4 GRADER CAP AS Mountain View Realty 1.6 Grade-Instructions Excel 2016 Project Step Instructions Points Possible 19 7 Apply the Light Blue Data Bars conditional formatting in the Gradient Fill section to the selling prices 20 7 21 Create a new conditional format that applies yellow (fourth color under Standard Colors) and bold font to values that contain 98% or higher for the Percent of List Price column Add 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 Select Landscape orientation and set appropriate margins so that the data will print on one page 5 22 23 Save and close the file Submit the file as directed 0 Total Points 100 AutoSave ORT has exploring.e04grades al (1) Protected View - File Insert Review Page Layout Formulas Data Home View Help Enable Edit PROTECTED VIEW Be careful files from the Internet can continues Unless you need to edit it's safer to stay in Proteded View E2 fi 725000 A B D C 1 Number Address City Selling Agent 1 2 8687 Kenwood Road Hernandez Cedar Hills 3 2 11 West Oak Circle American Fork Carey 4 3 314 Timp View Drive American Fork Goodrich 4 5 Lohi 614 West Cedar Drive Lugo 5 5 750 South Apple Way American Fork Goodrich 7 6 7 Kingston Court Alpine Carey 8 7 618 West Cedar Drive Lehi Lugo 9 8 321 North Choctaw Aenerican Fork Minkus 10 9 1370 Pinellas Road Alpine Lugo 11 10 1971 Grow Road Lohi Minkus 12 10 1971 Glenview Road Leht Minkus 13 15 West Oak Circle American Fork Hornandez 14 12 575 South Choctaw Alpine Goodrich 15 13 10995 SW 38 Court Lohi Goodrich 18 14 143 North Mountain View American Fork Carey 17 15 29 East Oak Circle American Fork Merlin 18 16 8030 Steeplechase Drive Alpine Merlin 19 17 2008 Cutwator Court Alpine Minkous 20 18 9876 South Sunset Avenue Eagle Mountain Hernandez 21 19 321 West Walnut Grove American Fork Goodrich 22 20 4081 Lybyer Avenue Alpine Minkus 23 20 4081 Lybyer Avenue Alpine Minkus 24 21 1900 Glenview Road Lehi Hernandez 25 9000 South Sunset Avenue Eagle Mountain Lugo 26 23 8432 South Sunso Avenue Eagle Mountain Minkus 22 24 831 South Weber American Fork Lugo 26 25 714 Timp View Lane American Fork Goodrich 29 20 240 East Jefferson Way Eagle Mountain Lugo 30 224 Rockaway Street Alpine Pijuan 31 20 260 East Jefferson Way Eagle Mountain Hernandez 32 29 200 East Jefferson Way Eagle Mountain Puan 33 30 8307 S Indian River Drive Lahi Hernandez 34 30 B307 S Indian River Drive Lahi 36 Hernandez 31 123 Ivy Lane Eagle Mountain Puan Sales Data Filtered Data List Price Selling Price Listing Date Sale Date $ 725,000 $ 645,250 4/1/2012 8/16/2012 $ 350.00 $ 340,000 4/1/2012 5/15/2012 $ 418.000 $ 400,000 4/1/2012 6/1/2012 $ 215,800 $ 200,000 4/1/2012 5/25/2012 $ 385,900 $ 385,900 4/1/2012 4/30/2012 $ 500,000 $ 465 000 4/3/2012 10/2012 $ 300,000 $ 290.000 4/3/2012 6/1/2012 $ 505,000 $ 535,000 4/6/2012 9/30/2012 $ 219,000 $ 215,000 4/8/2012 6/2/2012 $ 165,000 $ 156,750 4/12/2012 6/12/2012 $ 165,000 $ 156,750 4/12/2012 8/12/2012 $ 325 000 $ 320,000 4/12/2012 5/30/2012 $ 750,250 $ 700,000 4/12/2012 11/1/2052 $ 110,000 $ 1050 4/12/2012 7/3/2012 $ 314,250 $ 304,000 4/13/2012 8/5/2012 $ 555,000 $ 500,000 4/13/2012 10/15/2012 $ 450,000 $ 382,500 4/15/2012 6/30/2012 $ 345,000 $ 339,999 4/18/2012 6/23/2012 $ 300,000 $ 300,000 4/18/2012 4/30/2012 $ 425 000 $ 415,000 4/18/2012 6/30/2012 $ 325,000 $ 308.750 4/19/2012 7/2/2012 $ 325 000 $ 308.750 4/19/2012 7/2/2012 $ 325,000 $ 302 250 4/28/2012 6/1/2012 $ 325.000 $ 320 000 4/28/2012 5/28/2012 $ 285,750 $ 300 000 4/28/2012 8/3/2012 $ 425 815$ 400.000 4/28/2012 9/15/2012 $ 250,000 $ 232.000 4/29/2012 9/1/2012 $ 515.000 $ 485,750 4/29/2012 8/25/2012 $ 400,000 $ 375.000 4/30 2012 B/19/2012 $ 450.000 $ 400,000 4/30/2012 10/2012 $ 310 000 $ 300,000 4/30/2012 7/1/2012 $ 250,000 $ 255,000 5/1/2012 6/18/2012 $ 250 000 $ 255,000 5/1/2012 6/18/2012 $ 375,500 $ 375,500 5/1/2012 5/5/2012 A Type here to search O AutoSave Shass_exploring.-04.gader al Protected View - Home Insert Page Layout Formulas Data Review View Help 0 PROIECTED VIEW Be careful-files from the Internet can continues Unless you need to edit, it's safe to stay in Protected View Enable Editing EZ 34 35 00 38 39 40 41 42 44 45 40 A 30 31 32 33 34 35 38 37 38 39 10 40 41 42 43 44 45 46 47 48 49 50 50 51 52 49 49 50 51 52 53 54 X 725000 D 8307 S Indian River Di Lehi Hommandez 123 Ivy Lano Eagle Mountain Puan 9408 Forest Circle Cedar Hits Router 245 Ivy Lane Eagle Mountain Goodrich 11971 SW 209 Terrace Cedar Hills Carey 1857 Pine Drive Cedar Hits Hernande 3461 East London Way American Fork Router 18235 Orange Boulevard Cedar Hills Lugo 614 Lincoln Drive Eagle Mountain Reuter 2448 Woodacres Road Lei Hernandez 401 Pinecone Circle Alpine Goodrich 401 Pinecone Circle Alpine Goodrich 421 Ivy Lane Eagle Mountain Pa 765 Eat Sheridan Lane Eagle Mountain Minkus 61 Ead Walnut Grove American Fork Goodrich 1414 N Shendan Road Alpine P 487 Blue Skies Drive American Fork Goodrich 10700 Lake Share Lane Coder Hits Carey 73 East Oak Street Eagle Mountain Markin 81 Island Drive South Alpine Bethund 3412 Kimer Street Lohi Carey 605 Reservoir Drive Alpine Router 605 Reservoir Drive Alpine Router 77 East Oak Street Eagle Mountain Bethune 400 Ivy Lane Eagle Mountain Bethune 1 Southampton Place Lohi Goodrich 3418 North Sunset Lane Eagle Mountain Bethune 1892 North 250 West American Fork Carey 1629 NW 43rd Street Cedar Hills Carey 3400 North Sunset Lano Eagle Mountain Morkin 876 South California Way American Fork Hen 21 Compass Rood Cedar Hels Merkin 34 West Oakdey Drive American Fork Carey 34 West Ondoy Drive American Fork Carey 30 Kent Road Lohi Bethune Sales Data Filtered Data $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 3 $ $ $ $ E 250,000 5 375 400 185,500 $ 395,000 5 410,000 $ 500,700 $ 450,000 $ 395,000 $ 475,000 $ 450,000 $ 1,50), 120 $ 1,500 120$ 500 000 $ 480,750 $ 375,000 $ 1,250,000 $ 305,750 $ 650,000 $ 325 000 $ 147,800 $ 180 000 $ 310,000 5 310,000 $ 345,000 $ 375,000 $ 215,000 $ 450,000 $ 315 250 $ 475,000 $ 400,000 $ 316,000 $ 209.900 5 345,000 $ 345.000 $ 259.900 F 255.000 375,500 179,000 375,000 397,700 550,000 400,000 380,000 425,250 382,500 1,400,000 1.400,000 425,000 435,500 376,000 1.225 000 365,000 598,000 328,000 150 000 175,000 291,400 291,400 335,000 330.000 195.000 400.000 300.000 450.000 375,000 316.000 279,000 330,000 330.000 200905 G 5/1/2012 6/18/2012 5/1/2012 5/5/2012 5/1/2012 B/8/2012 5/1/2012 7/10/2012 5/1/2012 6/26/2012 5/1/2012 5/31/2012 5/2/2012 9/30/2012 2012 7/16/2012 5/5/2012 9/23/2012 5/5/2012 6/16/2012 5/5/2012 9/30/2012 5/5/2012 9/30/2012 5/5/2012 11/15/2012 5/5/2012 11/1/2012 5/6/2012 5/15/2012 5/12/2012 6/6/2012 5/13/2012 8/15/2012 5/15/2012 8/9/2012 5/18/2012 5/31/2012 5/22/2012 6/16/2012 5/23/2012 7/15/2012 5/22/2012 3/29/2012 5/22/2012 6/29/2012 5/23/2012 8/23/2012 5/20/2012 8/30/2012 5/28/2012 6/18/2012 5/28/2012 8/15/2012 5/28/2012 8/1/2012 5/31/2012 8/19/2012 5/31/2012 7/1502012 5/31/2012 6/30/2012 5/31/2012 6/1/2012 6/1/2012 8/15/2012 011/2012 8/15/2012 8/2/2012 92/2012 50 57 54 56 58 60 51 62 63 64 57 58 59 67 63 FF Type here to search O ESC AutoSave CH hesaploring,04 grader, al (1) - Protected View File Home Insert Page Layout Formulas Data Review View Help PROTECTED VIEW Be carefulles from the Intemen contain vies. Unless you need to edit it's safer to stay in Protected View Enable Editing 12 - X & 725000 A 64 08 67 88 70 70 72 73 74 75 7 77 ra 79 80 81 02 83 84 85 59 60 60 61 02 63 64 85 88 67 09 09 70 70 71 72 73 74 75 28 77 D 376 South Como Way American Fork Hon 21 Compass Road Cedar Hills Merkin 34 West Oakdey Drive Amonican Fork Carey 34 Wost Oy Drive American Fork Carey 30 Kont Road Lohi Bethune 140 East Sud Street Eagle Mountain Hen 160 West 5th Street Eagle Mountain Carey 240 Wed 5th Set Eagle Mountain Reuter 520 E Spring Street Alpine Minkus 678 Mountain Circle American Fork Goodrich 3490 North Sunset Lane Eagle Mountain Minkus 4916 Rock Spring Road Cedar Hils Lugo 8923 Harris Drive Alpine Minkus 876 West Hadley Way American Fork Hernandez 876 West Hadley Way American Fork Hernandez 1132 SW 52nd Street Cedar Hills Hernandez 416 East Oak Eagle Mountain Morkin 10 Vostal Drive Cedar Hills Carey 425 East Oak Eagle Mountain Bethune 876 West Holiday Alpine Goodrich 103 Jasper Drive Lohi Bethune 516 East Oak Eagle Mountain Puan 123 Oak Avenue American Fork Carey 600 Ivy Lane Eagle Mountain Puan 144 Oak Avenue American Fork Goodrich 144 Oak Avenue American Fork Goodrich 1600 Reeves Street Codar Hills Router $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 316,000 $ 209 900 $ 345,000 $ 345,000 $ 259,900 $ 399,000 $ 410,000 $ 285,750 $ 189,900 $ 335 000 $ 250,000 $ 275,000 $ 589,000 $ 345,670 $ 345,670 $ 254,500 $ 300,000 $ 555,000 $ 275,900 $ 475,000 $ 165 900$ 299.999 $ 400.000 $ 339.999 $ 380.500$ 380 500 5 245 900 $ F 316,000 279,000 3330.000 330.000 246,905 350,000 350, 750 300,000 186, 102 330,000 275,000 204,000 575,000 345,000 345.000 236.685 250.000 565,000 250,000 455.000 159.204 280 000 400,000 310,000 365,000 385,000 233 605 G H 5/31/2012 01/2012 5/31/2012 6/1/2012 6/1/2012 8/15/2012 011/2012 8/15/2012 8/2/2012 9/2/2012 6/2/2012 11/2012 6/3/2012 10/15/2012 6/5/2012 7/1/2012 318/2012 7/26/2012 6/10/2012 9/15/2012 6/12/2012 6/25/2012 6/13/2012 7/21/2012 6/18/2012 7/8/2012 6/20/2012 9/30/2012 6/20/2012 9/30/2012 6/23/2012 9/21/2012 0/23/2012 11/2/2012 6/24/2012 6/30/2012 6/24/2012 315/2012 6/24/2012 9/18/2012 8/29/2012 96/2012 6/29/2012 9/30/2012 6/30/2012 7/15/2012 6/30/2012 10/1/2012 6/30/2012 9/1/2012 6/30/2012 9/1/2012 6/30/2012 W162012 sees 80 BO 81 B7 89 09 90 91 92 93 94 95 90 97 98 Sales Data Filtered Data ER Type here to search O TI