57 3400 North Sunset Lane Eagle Mountain Merkirn 58 876 South California Way American Fork Hen 59 21 Compass Road 60 34 West Oakley Drive 60 34 West Oakley Drive 61 30 Kent Road 62 140 East 3rd Street 63 160 West 5th Street 64 240 West 5th Street 65 520 E Spring Street 66 678 Mountain Circle 67 3490 North Sunset Lane Eagle Mountain Minkus 68 4916 Rock Spring Road Cedar Hills 69 8923 Harris Drive 70 876 West Hadley Way 70 876 West Hadley Way 71 1132 SW 52nd Street $400,000 $316,000 $289,900 $345,000 $345,000 $259,900 $399,000 10,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 $245,900 $375,000 $316,000 $279,000 $330,000 $330,000 $246,905 $350,000 $350,750 $300,000 5/31/12 5/31/12 American ForkCarey American Fork Carey Bethune 9/2/12 Eagle Mountain Hen Eagle Mountain Carey Eagle Mountain Reuter 11/30/12 6/5/12 Minkus 7/26/12 American ForkGoodrich $330,000 $275,000 $264,000 $575,000 $345,000 $345,000 $236,685 $250,000 $565,000 $250,000 $455,000 6/25/12 7/21/12 7/8/12 9/30/12 American Fork Hernandez American ForkHernandez Hernandez 6/20/12 6/20/12 6/23/12 6/23/12 Eagle Mountain Merkin 11/2/12 73 10 Vestal Drive 74 425 East Oak 75 876 West Holiday 76 103 Jasper Drive 77 516 East Oak 78 123 Oak Avenue 79 600 Ivy Lane 80 144 Oak Avenue 80 144 Oak Avenue 81 1600 Reeves Street 6/30/12 Eagle Mountain Bethune 9/18/12 Bethune Eagle Mountain Pijuan American Fork Carey Eagle Mountain Pijuan American Fork Goodrich American Fork Goodrich $280,000 $400,000 $310,000 $365,000 $365,000 $233,605 6/29/12 6/29/12 6/30/12 6/30/12 6/30/12 6/30/12 6/30/12 7/15/12 $310,000 $250,000 $250,000 $375,500 $185,500 $395,000 29 290 East Jefferson Way 30 8307 S Indian River Drive 30 8307 S Indian River Drive Eagle Mountain Pijuan Lehi Lehi Eagle Mountain Pijuan Cedar Hills Eagle Mountain Goodrich Cedar Hills $300,000 $255,000 $255,000 $375,500 $179,000 $375,000 $397,700 $550,000 4/30/12 Hernandez Hernandez 5/5/12 6/6/12 32 9408 Forest Hills Circle 33 245 Ivy Lane 34 11971 SW 269 Terrace 35 1857 Pine Drive 36 3461 East Lindon Way 37 16235 Orange Boulevard 38 614 Lincoln Drive 39 2448 Woodacres Road 40 401 Pinecone Circle 40 401 Pinecone Circle 6/26/12 Hernandez $560,700 American Fork Reuter Cedar Hills Eagle Mountain Reuter $395,000 $475,000 $450,000 $1,500,120 120 $500,000 $460,750 $375,000 $1,250,000 $365,750 $650,000 $325,000 $147,800 $180,000 $310,000 $310,000 $345,000 $375,000 $215,000 $450,000 $315,250 $475,000 $380,000 $425,250 $382,500 $1,400,000 $1,400,000 $425,000 $435,500 $376,000 $1,225,000 $355,000 $598,000 $325,000 $150,000 5/5/12 5/5/12 5/5/12 5/5/12 5/5/12 5/5/12 5/5/12 5/6/12 7/16/12 9/23/12 Hernandez Goodrich Goodrich 9/30/12 11/15/12 Eagle Mountain Pijuan Eagle Mountain Minkus American Fork Goodrich 42 765 East Sheridan Lane 43 61 East Walnut Grove 44 1414 N Sheridan Road 45 487 Blue Skies Drive 46 10700 Lake Shore Lane 47 73 East Oak Street 48 81 Island Drive South 49 3412 Kilmer Street 50 605 Reservoir Drive 50 605 Reservoir Drive 51 77 East Oak Street 52 400 Ivy Lane 53 1 Southampton Place 54 3418 North Sunset Lane 55 1892 North 250 West 56 1629 NW 43rd Street Pijuan 6/6/12 American Fork Goodrich Cedar Hills Eagle Mountain Merkin 5/31/12 Bethune 5/22/12 $291,400 $291,400 $335,000 $330,000 $195,000 $400,000 $300,000 $450,000 6/29/12 6/29/12 6/23/12 5/22/12 Eagle Mountain Bethune Eagle Mountain Bethune 5/23/12 Goodriclh Eagle Mountain Bethune American ForkCarey Cedar Hills Address Selling AgentList Price Hernandez Selling Price Listing Date $725,000 $350,000 $645,250 $340,000 $400,000 $200,000 $385,900 $465,000 $290,000 $535,000 $215,000 $156,750 1 8687 Kenwood Road 2 11 West Oak Circle American Fork Carey American ForkGoodrich 3 314 Timp View Drive $215,800 $385,900 $500,000 $300,000 $565,000 $219,000 $165,000 $165,000 $325,000 $750,250 5/25/12 4/30/12 4 614 West Cedar Drive 5 750 South Apple Way American Fork Goodrich 4/3/12 4/3/12 4/6/12 6 7 Kingston Court 7 618 West Cedar Drive 8 321 North Choctaw American Fork Minkus 9/30/12 9 1370 Pinellas Road 10 1971 Glenview Road 10 1971 Glenview Road 11 15 West Oak Circle 12 575 South Choctaw 13 10995 SW 88 Court 14 143 North Mountain Vie American Fork Carey 15 29 East Oak Circle 16 8030 Steeplechase Drive Alpine 17 2006 Cutwater Court 18 9876 South Sunset Avenue Eagle Mountain Hernandez 19 321 West Walnut Grove American Fork Goodrich 20 4081 Lybyer Avenue 20 4081 Lybyer Avenue Minkus $320,000 $700,000 $106,000 $304,000 $500,000 $382,500 $339,999 $300,000 $415,000 $308,750 Am erican Fork Hernandez 5/30/12 Goodrich Goodrich 7/3/12 8/5/12 10/15/12 6/30/12 6/23/12 4/30/12 6/30/12 7/2/12 $314,250 $555,000 $450,000 $345,000 $300,000 $425,000 $325,000 $325,000 $325,000 $325,000 $285,750 $425,815 $250,000 $515,000 $400,000 $450,000 American Fork Merkin ne $302,250 $320,000 $300,000 $400,000 $232,000 $485,750 Hernandez 22 9000 South Sunset Avenue Eagle Mountain Lugo 23 8432 South Sunset Avenue Eagle Mountain Minkus 24 831 South Weber 4/28/12 5/28/12 American Fork Lugo American ForkGoodrich Eagle Mountain Lugo 4/28/12 9/15/12 4/29/12 26 240 East Jefferson Way 27 224 Rockaway Street 28 260 East Jefferson Way 8/25/12 ne Eagle Mountain Hernandez $400,000 4/30/12 10/1/12 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: Points Possible Step Instructions 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 5 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 10 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 12 13 14 Adjust column widths so that all data is visible. Wrap the column labels Repeat the field names on all pages 4 Add a page break at Row 36 so that the city data does not span between pages, and return to Normal view, if necessary 15 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 16 Display the Filtered Data worksheet. Filter the data to display the cities of Alpine Cedar Hills and Eagle Mountain 17 Filter the data to display records for houses that were on the market 30 days or more 5 18 Apply the conditional formatting 3 Arrows (Colored) icon set to the Days on Market values Grader Instructions Excel 2016 Project Points Possible Step Instructions Apply the Light Blue Data Bars conditional formatting in the Gradient Fill section to the selling prices 19 20 Create a new conditional format that applies yellow fill (fourth color under Standard Colors) and bold font to values that contain 98% or higher for the Percent of List Price column. 21 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. 22 Select Landscape orientation and set appropriate margins so that the data will print on one page. 4 23 Save and close the file. Submit the file as directed. Total Points 100 57 3400 North Sunset Lane Eagle Mountain Merkirn 58 876 South California Way American Fork Hen 59 21 Compass Road 60 34 West Oakley Drive 60 34 West Oakley Drive 61 30 Kent Road 62 140 East 3rd Street 63 160 West 5th Street 64 240 West 5th Street 65 520 E Spring Street 66 678 Mountain Circle 67 3490 North Sunset Lane Eagle Mountain Minkus 68 4916 Rock Spring Road Cedar Hills 69 8923 Harris Drive 70 876 West Hadley Way 70 876 West Hadley Way 71 1132 SW 52nd Street $400,000 $316,000 $289,900 $345,000 $345,000 $259,900 $399,000 10,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 $245,900 $375,000 $316,000 $279,000 $330,000 $330,000 $246,905 $350,000 $350,750 $300,000 5/31/12 5/31/12 American ForkCarey American Fork Carey Bethune 9/2/12 Eagle Mountain Hen Eagle Mountain Carey Eagle Mountain Reuter 11/30/12 6/5/12 Minkus 7/26/12 American ForkGoodrich $330,000 $275,000 $264,000 $575,000 $345,000 $345,000 $236,685 $250,000 $565,000 $250,000 $455,000 6/25/12 7/21/12 7/8/12 9/30/12 American Fork Hernandez American ForkHernandez Hernandez 6/20/12 6/20/12 6/23/12 6/23/12 Eagle Mountain Merkin 11/2/12 73 10 Vestal Drive 74 425 East Oak 75 876 West Holiday 76 103 Jasper Drive 77 516 East Oak 78 123 Oak Avenue 79 600 Ivy Lane 80 144 Oak Avenue 80 144 Oak Avenue 81 1600 Reeves Street 6/30/12 Eagle Mountain Bethune 9/18/12 Bethune Eagle Mountain Pijuan American Fork Carey Eagle Mountain Pijuan American Fork Goodrich American Fork Goodrich $280,000 $400,000 $310,000 $365,000 $365,000 $233,605 6/29/12 6/29/12 6/30/12 6/30/12 6/30/12 6/30/12 6/30/12 7/15/12 $310,000 $250,000 $250,000 $375,500 $185,500 $395,000 29 290 East Jefferson Way 30 8307 S Indian River Drive 30 8307 S Indian River Drive Eagle Mountain Pijuan Lehi Lehi Eagle Mountain Pijuan Cedar Hills Eagle Mountain Goodrich Cedar Hills $300,000 $255,000 $255,000 $375,500 $179,000 $375,000 $397,700 $550,000 4/30/12 Hernandez Hernandez 5/5/12 6/6/12 32 9408 Forest Hills Circle 33 245 Ivy Lane 34 11971 SW 269 Terrace 35 1857 Pine Drive 36 3461 East Lindon Way 37 16235 Orange Boulevard 38 614 Lincoln Drive 39 2448 Woodacres Road 40 401 Pinecone Circle 40 401 Pinecone Circle 6/26/12 Hernandez $560,700 American Fork Reuter Cedar Hills Eagle Mountain Reuter $395,000 $475,000 $450,000 $1,500,120 120 $500,000 $460,750 $375,000 $1,250,000 $365,750 $650,000 $325,000 $147,800 $180,000 $310,000 $310,000 $345,000 $375,000 $215,000 $450,000 $315,250 $475,000 $380,000 $425,250 $382,500 $1,400,000 $1,400,000 $425,000 $435,500 $376,000 $1,225,000 $355,000 $598,000 $325,000 $150,000 5/5/12 5/5/12 5/5/12 5/5/12 5/5/12 5/5/12 5/5/12 5/6/12 7/16/12 9/23/12 Hernandez Goodrich Goodrich 9/30/12 11/15/12 Eagle Mountain Pijuan Eagle Mountain Minkus American Fork Goodrich 42 765 East Sheridan Lane 43 61 East Walnut Grove 44 1414 N Sheridan Road 45 487 Blue Skies Drive 46 10700 Lake Shore Lane 47 73 East Oak Street 48 81 Island Drive South 49 3412 Kilmer Street 50 605 Reservoir Drive 50 605 Reservoir Drive 51 77 East Oak Street 52 400 Ivy Lane 53 1 Southampton Place 54 3418 North Sunset Lane 55 1892 North 250 West 56 1629 NW 43rd Street Pijuan 6/6/12 American Fork Goodrich Cedar Hills Eagle Mountain Merkin 5/31/12 Bethune 5/22/12 $291,400 $291,400 $335,000 $330,000 $195,000 $400,000 $300,000 $450,000 6/29/12 6/29/12 6/23/12 5/22/12 Eagle Mountain Bethune Eagle Mountain Bethune 5/23/12 Goodriclh Eagle Mountain Bethune American ForkCarey Cedar Hills Address Selling AgentList Price Hernandez Selling Price Listing Date $725,000 $350,000 $645,250 $340,000 $400,000 $200,000 $385,900 $465,000 $290,000 $535,000 $215,000 $156,750 1 8687 Kenwood Road 2 11 West Oak Circle American Fork Carey American ForkGoodrich 3 314 Timp View Drive $215,800 $385,900 $500,000 $300,000 $565,000 $219,000 $165,000 $165,000 $325,000 $750,250 5/25/12 4/30/12 4 614 West Cedar Drive 5 750 South Apple Way American Fork Goodrich 4/3/12 4/3/12 4/6/12 6 7 Kingston Court 7 618 West Cedar Drive 8 321 North Choctaw American Fork Minkus 9/30/12 9 1370 Pinellas Road 10 1971 Glenview Road 10 1971 Glenview Road 11 15 West Oak Circle 12 575 South Choctaw 13 10995 SW 88 Court 14 143 North Mountain Vie American Fork Carey 15 29 East Oak Circle 16 8030 Steeplechase Drive Alpine 17 2006 Cutwater Court 18 9876 South Sunset Avenue Eagle Mountain Hernandez 19 321 West Walnut Grove American Fork Goodrich 20 4081 Lybyer Avenue 20 4081 Lybyer Avenue Minkus $320,000 $700,000 $106,000 $304,000 $500,000 $382,500 $339,999 $300,000 $415,000 $308,750 Am erican Fork Hernandez 5/30/12 Goodrich Goodrich 7/3/12 8/5/12 10/15/12 6/30/12 6/23/12 4/30/12 6/30/12 7/2/12 $314,250 $555,000 $450,000 $345,000 $300,000 $425,000 $325,000 $325,000 $325,000 $325,000 $285,750 $425,815 $250,000 $515,000 $400,000 $450,000 American Fork Merkin ne $302,250 $320,000 $300,000 $400,000 $232,000 $485,750 Hernandez 22 9000 South Sunset Avenue Eagle Mountain Lugo 23 8432 South Sunset Avenue Eagle Mountain Minkus 24 831 South Weber 4/28/12 5/28/12 American Fork Lugo American ForkGoodrich Eagle Mountain Lugo 4/28/12 9/15/12 4/29/12 26 240 East Jefferson Way 27 224 Rockaway Street 28 260 East Jefferson Way 8/25/12 ne Eagle Mountain Hernandez $400,000 4/30/12 10/1/12 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: Points Possible Step Instructions 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 5 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 10 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 12 13 14 Adjust column widths so that all data is visible. Wrap the column labels Repeat the field names on all pages 4 Add a page break at Row 36 so that the city data does not span between pages, and return to Normal view, if necessary 15 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 16 Display the Filtered Data worksheet. Filter the data to display the cities of Alpine Cedar Hills and Eagle Mountain 17 Filter the data to display records for houses that were on the market 30 days or more 5 18 Apply the conditional formatting 3 Arrows (Colored) icon set to the Days on Market values Grader Instructions Excel 2016 Project Points Possible Step Instructions Apply the Light Blue Data Bars conditional formatting in the Gradient Fill section to the selling prices 19 20 Create a new conditional format that applies yellow fill (fourth color under Standard Colors) and bold font to values that contain 98% or higher for the Percent of List Price column. 21 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. 22 Select Landscape orientation and set appropriate margins so that the data will print on one page. 4 23 Save and close the file. Submit the file as directed. Total Points 100
