Office 2013 - myitlab:grader - Instructions Exploring - Excel Chapter 4: Homework Project 1 Mountain View Realty Project Description: A coworker developed a spreadsheet listing houses listed and sold during the past several months. She included addresses, location, list price, selling price, listing date, and date sold. You need 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 format the table. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Points Possible Instructions 1 Start Excel. Open the downloaded file named exploring_e04_grader_h1.xlsx. 0 2 In the Sales Data worksheet, freeze the top row. Hint: On the VIEW tab, in the Window group, click Freeze Panes and then click Freeze Top Row. 5 3 In the Sales Data worksheet, convert the data to a table and apply Table Style Medium 17. Hint: To convert the data, on the INSERT tab, in the Tables group, click Table. To apply the style, on the DESIGN tab, in the Table Styles group, click the More button. 6 4 In the Sales Data worksheet, remove duplicate records in the table. Hint: On the DESIGN tab, in the Tools group, click Remove Duplicates. 5 5 In the Sales Data worksheet, insert a new field to the right of the Selling Price field. Name the new field Percent of List Price. Hint: Right-click the Column E header and click Insert. 6 6 7 8 In the Sales Data worksheet, create a formula in cell E2 with structured references to calculate what percent the selling price is of the list price. Format the field with Percent Style with one decimal place in the range E2:E43. Hint: In cell E2, enter the formula =[Selling Price]/[List Price]. To format the range, on the HOME tab, in the Number group, click the Number Format Dialog Box Launcher. In the Sales Data worksheet, 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. Hint: In cell H1, type Days on Market and press ENTER. To insert the formula, in cell H2, type =[Sale Date]-[Listing Date]. In the Sales Data worksheet, add a total row to display the average percent of list price and average number of days on market. Apply the General number format to the average number of days on market. Type Averages in cell A44. Hint: To display the Total row, on the DESIGN tab, in the Table Style Options group, click Total Row. To select the function, click the cell, click the arrow, and then click the function. 6 6 10 9 In the Sales Data worksheet, 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. Hint: On the HOME tab, in the Editing group, click Sort & Filter. 8 10 In the Sales Data worksheet, select the Listing Date and Sale Date fields and set a column width of 11.00. Wrap the column labels in the range E1:H1. Hint: To set the column width, on the HOME tab, in the Cells group, click Format. To wrap the labels, in the Alignment group, click Wrap Text. 6 Updated: 04/04/2013 1 E_CH04_EXPV1_H1_Instructions.docx Office 2013 - myitlab:grader - Instructions Exploring - Excel Chapter 4: Homework Project 1 Step Instructions Points Possible 11 Set up the Sales Data worksheet so that when printed, the first row containing the field names will print as titles on each page. Hint: On the PAGE LAYOUT tab, in the Page Setup group, click Print Titles. 5 12 Display the Sales Data worksheet in Page Break Preview and move the page break to occur between rows 26 and 27, and then change back to Normal view. Hint: To display Page Break Preview, on the VIEW tab, in the Workbook Views group, click Page Break Preview. To insert the page break, click cell A27 and on the PAGE LAYOUT tab, in the Page Setup group, click Breaks. 5 13 Display the Filtered Data worksheet. Convert the table to a range of data. Hint: On the DESIGN tab, in the Tools group, click Convert to Range. 5 14 Filter the data to display the cities of Alpine, Cedar Hills, and Eagle Mountain. Hint: On the HOME tab, in the Editing group, click Sort & Filter. 6 15 Use a custom AutoFilter to display records for houses that were on the market 30 days or more. 5 16 Apply the 3 Arrows (Colored) icon set to the days on market field values. Do not include the field name in the range. Hint: On the HOME tab, in the Styles group, click Conditional Formatting and then point to Icon Sets. 5 17 Apply the Light Blue Data Bar conditional formatting in the Gradient Fill section to the selling prices. Hint: On the HOME tab, in the Styles group, click Conditional Formatting and then point to Data Bars. 5 18 Create a new conditional format that applies Yellow fill (fourth color in the bottom row) and bold font to values that are higher than 98% for the Percent of List Price column. Hint: On the HOME tab, in the Styles group, click Conditional Formatting and then click New Rule. 6 19 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Sales Data, Filtered Data. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0 Total Points Updated: 04/04/2013 2 100 E_CH04_EXPV1_H1_Instructions.docx Address City 8687 Kenwood Road Cedar Hills 11 West Oak Circle American Fork 614 West Cedar Drive Lehi 750 South Apple Way American Fork 7 Kingston Court Alpine 321 North Choctaw American Fork 1370 Pinellas Road Alpine 1971 Glenview Road Lehi 575 South Choctaw Alpine 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 224 Rockaway Street Alpine 260 East Jefferson Way Eagle Mountain 260 East Jefferson Way Eagle Mountain 8307 S Indian River Drive Lehi 123 Ivy Lane Eagle Mountain 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 421 Ivy Lane Eagle Mountain 765 East Sheridan Lane Eagle Mountain 1414 N Sheridan Road Alpine 487 Blue Skies Drive American Fork 10700 Lake Shore Lane Cedar Hills 73 East Oak Street Eagle Mountain $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ List Price 725,000 350,000 215,800 385,900 500,000 565,000 219,000 165,000 750,250 314,250 555,000 450,000 345,000 300,000 425,000 325,000 325,000 325,000 285,750 425,815 250,000 400,000 450,000 450,000 250,000 375,500 395,000 410,000 560,700 450,000 395,000 475,000 500,000 460,750 1,250,000 365,750 650,000 325,000 Selling Price Listing Date Sale Date $ 645,250 4/1/2016 6/16/2016 $ 340,000 4/1/2016 5/15/2016 $ 200,000 4/1/2016 5/25/2016 $ 385,900 4/1/2016 4/30/2016 $ 465,000 4/3/2016 6/10/2016 $ 535,000 4/6/2016 9/30/2016 $ 215,000 4/8/2016 6/2/2016 $ 156,750 4/12/2016 6/12/2016 $ 700,000 4/12/2016 11/1/2016 $ 304,000 4/13/2016 8/5/2016 $ 500,000 4/13/2016 10/15/2016 $ 382,500 4/15/2016 6/30/2016 $ 339,999 4/18/2016 6/23/2016 $ 300,000 4/18/2016 4/30/2016 $ 415,000 4/18/2016 6/30/2016 $ 308,750 4/19/2016 7/2/2016 $ 302,250 4/28/2016 6/1/2016 $ 320,000 4/28/2016 5/28/2016 $ 300,000 4/28/2016 6/3/2016 $ 400,000 4/28/2016 9/15/2016 $ 232,000 4/29/2016 9/1/2016 $ 375,000 4/30/2016 6/19/2016 $ 400,000 4/30/2016 10/1/2016 $ 400,000 4/30/2016 10/1/2016 $ 255,000 5/1/2016 6/18/2016 $ 375,500 5/1/2016 5/5/2016 $ 375,000 5/1/2016 7/16/2016 $ 397,700 5/1/2016 6/26/2016 $ 550,000 5/1/2016 5/30/2016 $ 400,000 5/2/2016 9/30/2016 $ 380,000 5/5/2016 7/16/2016 $ 425,250 5/5/2016 9/23/2016 $ 425,000 5/5/2016 11/15/2016 $ 435,500 5/5/2016 11/1/2016 $ 1,225,000 5/12/2016 6/6/2016 $ 355,000 5/13/2016 8/15/2016 $ 598,000 5/15/2016 6/9/2016 $ 325,000 5/18/2016 5/31/2016 73 East Oak Street 3412 Kilmer Street 77 East Oak Street 1629 NW 43rd Street 3400 North Sunset Lane 876 South California Way Eagle Mountain Lehi Eagle Mountain Cedar Hills Eagle Mountain American Fork $ $ $ $ $ $ 325,000 180,000 345,000 475,000 400,000 316,000 $ $ $ $ $ $ 325,000 175,000 335,000 450,000 375,000 316,000 5/18/2016 5/23/2016 5/23/2016 5/31/2016 5/31/2016 5/31/2016 5/31/2016 7/15/2016 6/23/2016 8/19/2016 7/15/2016 6/30/2016 City List Price $ 5,00,00 0 $ Alpine 2,19,00 0 $ Alpine 7,50,25 0 $ Alpine 4,50,00 0 $ Alpine 3,45,00 0 $ Alpine 3,25,00 0 $ Alpine 4,00,00 0 $ Alpine 15,00,1 20 $ Alpine 3,10,00 0 $ Alpine 1,89,90 0 $ Alpine 12,50,0 00 $ Alpine 1,47,80 0 $ Alpine 5,89,00 0 $ America 5,55,00 n Fork 0 $ America 5,65,00 n Fork 0 $ America 4,50,00 n Fork 0 $ America 4,25,81 n Fork 5 $ America 2,50,00 n Fork 0 $ America 3,14,25 n Fork 0 $ America 3,45,67 n Fork 0 $ America 3,35,00 n Fork 0 $ America 3,65,75 n Fork 0 Alpine Selling Price $ 4,65,00 0 $ 2,15,00 0 $ 7,00,00 0 $ 3,82,50 0 $ 3,39,99 9 $ 3,08,75 0 $ 3,75,00 0 $ 14,00,0 00 $ 2,91,40 0 $ 1,86,10 2 $ 12,25,0 00 $ 1,50,00 0 $ 5,75,00 0 $ 5,00,00 0 $ 5,35,00 0 $ 4,00,00 0 $ 4,00,00 0 $ 2,32,00 0 $ 3,04,00 0 $ 3,45,00 0 $ 3,30,00 0 $ 3,55,00 0 Percent Days on of List Market Price 93.00% 76 98.20% 76 93.30% 76 85.00% 76 98.60% 76 95.00% 76 93.80% 76 93.30% 76 94.00% 76 98.00% 76 98.00% 25 ### 25 97.60% 20 90.10% 185 94.70% 177 88.90% 151 93.90% 140 92.80% 125 96.70% 114 99.80% 102 98.50% 97 97.10% 94 $ America 3,45,00 n Fork 0 $ America 4,25,00 n Fork 0 $ America 3,15,25 n Fork 0 $ America 4,18,00 n Fork 0 $ America 3,25,00 n Fork 0 $ America 3,50,00 n Fork 0 $ America 3,16,00 n Fork 0 $ America 3,85,90 n Fork 0 $ America 3,75,00 n Fork 0 $ Cedar 7,25,00 Hills 0 $ Cedar 1,85,50 Hills 0 $ Cedar 4,10,00 Hills 0 $ Cedar 3,95,00 Hills 0 $ Cedar 4,75,00 Hills 0 $ Cedar 2,75,00 Hills 0 $ Cedar 5,60,70 Hills 0 $ Cedar 6,50,00 Hills 0 $ Cedar 2,89,90 Hills 0 Eagle $ Mountai 3,25,00 n 0 $ 3,30,00 0 $ 4,15,00 0 $ 3,00,00 0 $ 4,00,00 0 $ 3,20,00 0 $ 3,40,00 0 $ 3,16,00 0 $ 3,85,90 0 $ 3,76,00 0 $ 6,45,25 0 $ 1,79,00 0 $ 3,97,70 0 $ 3,80,00 0 $ 4,50,00 0 $ 2,64,00 0 $ 5,50,00 0 $ 5,98,00 0 $ 2,79,00 0 $ 3,20,00 0 95.70% 75 97.60% 73 95.20% 65 95.70% 61 98.50% 48 97.10% 44 ### 30 ### 29 ### 9 89.00% 76 96.50% 76 97.00% 76 96.20% 76 94.70% 76 96.00% 76 98.10% 29 92.00% 25 96.20% 1 98.50% 76 Eagle $ $ Mountai 2,85,75 3,00,00 ### n 0 0 76 Eagle $ $ Mountai 5,15,00 4,85,75 94.30% 76 n 0 0 Eagle $ $ Mountai 4,50,00 4,00,00 88.90% 76 n 0 0 Eagle $ $ Mountai 3,10,00 3,00,00 96.80% 76 n 0 0 Eagle $ $ Mountai 3,95,00 3,75,00 94.90% 76 n 0 0 Eagle $ $ Mountai 4,75,00 4,25,25 89.50% 76 n 0 0 Eagle $ $ Mountai 5,00,00 4,25,00 85.00% 76 n 0 0 Eagle $ $ Mountai 4,60,75 4,35,50 94.50% 76 n 0 0 Eagle $ $ Mountai 3,45,00 3,35,00 97.10% 76 n 0 0 Eagle $ $ Mountai 3,75,00 3,30,00 88.00% 76 n 0 0 Eagle $ $ Mountai 4,00,00 3,75,00 93.80% 76 n 0 0 Eagle $ $ Mountai 3,99,00 3,50,00 87.70% 76 n 0 0 Eagle $ $ Mountai 4,10,00 3,50,75 85.50% 76 n 0 0 Eagle $ $ Mountai 2,85,75 3,00,00 ### n 0 0 26 Eagle $ $ Mountai 3,25,00 3,25,00 ### n 0 0 13 Eagle $ $ Mountai 2,50,00 2,75,00 ### n 0 0 13 Eagle $ $ Mountai 3,00,00 3,00,00 ### n 0 0 12 Eagle $ Mountai 3,75,50 n 0 $ Lehi 2,59,90 0 $ Lehi 1,10,00 0 $ 3,75,50 ### 4 0 $ 2,46,90 95.00% 92 5 $ 1,06,00 96.40% 82 0 $ Lehi 1,65,00 0 $ Lehi 3,00,00 0 $ Lehi 2,15,80 0 $ Lehi 1,80,00 0 $ Lehi 2,50,00 0 $ Lehi 4,50,00 0 $ Lehi 3,25,00 0 $ Lehi 2,15,00 0 Average 95.40% $ 1,56,75 0 $ 2,90,00 0 $ 2,00,00 0 $ 1,75,00 0 $ 2,55,00 0 $ 3,82,50 0 $ 3,02,25 0 $ 1,95,00 0 67 95.00% 61 96.70% 59 92.70% 54 97.20% 53 ### 48 85.00% 42 93.00% 34 90.70% 21 City List Price Selling Price Alpine Alpine Alpine Alpine Alpine Alpine Alpine Alpine Alpine Alpine Alpine Alpine Alpine Cedar Hills Cedar Hills Cedar Hills Cedar Hills Cedar Hills Cedar Hills Cedar Hills Cedar Hills Cedar Hills Eagle Mountain Eagle Mountain Eagle Mountain Eagle Mountain $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 5,00,000 2,19,000 7,50,250 4,50,000 3,45,000 3,25,000 4,00,000 15,00,120 3,10,000 1,89,900 12,50,000 1,47,800 5,89,000 7,25,000 1,85,500 4,10,000 3,95,000 4,75,000 2,75,000 5,60,700 6,50,000 2,89,900 4,65,000 2,15,000 7,00,000 3,82,500 3,39,999 3,08,750 3,75,000 14,00,000 2,91,400 1,86,102 12,25,000 1,50,000 5,75,000 6,45,250 1,79,000 3,97,700 3,80,000 4,50,000 2,64,000 5,50,000 5,98,000 2,79,000 Percent of List Days on Price Market 93.00% 76 98.20% 76 93.30% 76 85.00% 76 98.60% 76 95.00% 76 93.80% 76 93.30% 76 94.00% 76 98.00% 76 98.00% 25 101.50% 25 97.60% 20 89.00% 76 96.50% 76 97.00% 76 96.20% 76 94.70% 76 96.00% 76 98.10% 29 92.00% 25 96.20% 1 $ 3,25,000 $ 3,20,000 98.50% 76 $ 2,85,750 $ 3,00,000 105.00% 76 $ 5,15,000 $ 4,85,750 94.30% 76 $ 4,50,000 $ 4,00,000 88.90% 76 Eagle Mountain $ 3,10,000 $ 3,00,000 96.80% 76 City List Price $ 5,00,00 0 $ Alpine 2,19,00 0 $ Alpine 7,50,25 0 $ Alpine 4,50,00 0 $ Alpine 3,45,00 0 $ Alpine 3,25,00 0 $ Alpine 4,00,00 0 $ Alpine 15,00,1 20 $ Alpine 3,10,00 0 $ Alpine 1,89,90 0 $ America 5,55,00 n Fork 0 $ America 5,65,00 n Fork 0 $ America 4,50,00 n Fork 0 $ America 4,25,81 n Fork 5 $ America 2,50,00 n Fork 0 $ America 3,14,25 n Fork 0 $ America 3,45,67 n Fork 0 $ America 3,35,00 n Fork 0 $ America 3,65,75 n Fork 0 $ America 3,45,00 n Fork 0 $ America 4,25,00 n Fork 0 $ America 3,15,25 n Fork 0 $ America 4,18,00 n Fork 0 Alpine Selling Price $ 4,65,00 0 $ 2,15,00 0 $ 7,00,00 0 $ 3,82,50 0 $ 3,39,99 9 $ 3,08,75 0 $ 3,75,00 0 $ 14,00,0 00 $ 2,91,40 0 $ 1,86,10 2 $ 5,00,00 0 $ 5,35,00 0 $ 4,00,00 0 $ 4,00,00 0 $ 2,32,00 0 $ 3,04,00 0 $ 3,45,00 0 $ 3,30,00 0 $ 3,55,00 0 $ 3,30,00 0 $ 4,15,00 0 $ 3,00,00 0 $ 4,00,00 0 Percent Days on of List Market Price 93.00% 76 98.20% 76 93.30% 76 85.00% 76 98.60% 76 95.00% 76 93.80% 76 93.30% 76 94.00% 76 98.00% 76 90.10% 185 94.70% 177 88.90% 151 93.90% 140 92.80% 125 96.70% 114 99.80% 102 98.50% 97 97.10% 94 95.70% 75 97.60% 73 95.20% 65 95.70% 61 $ America 3,25,00 n Fork 0 $ America 3,50,00 n Fork 0 $ America 3,16,00 n Fork 0 Average 95.20% $ Cedar 7,25,00 Hills 0 $ Cedar 1,85,50 Hills 0 $ Cedar 4,10,00 Hills 0 $ Cedar 3,95,00 Hills 0 $ Cedar 4,75,00 Hills 0 $ Cedar 2,75,00 Hills 0 Eagle $ Mountai 3,25,00 n 0 $ 3,20,00 0 $ 3,40,00 0 $ 3,16,00 0 90 $ 6,45,25 0 $ 1,79,00 0 $ 3,97,70 0 $ 3,80,00 0 $ 4,50,00 0 $ 2,64,00 0 $ 3,20,00 0 98.50% 48 97.10% 44 ### 30 89.00% 76 96.50% 76 97.00% 76 96.20% 76 94.70% 76 96.00% 76 98.50% 76 Eagle $ $ Mountai 2,85,75 3,00,00 ### n 0 0 76 Eagle $ $ Mountai 5,15,00 4,85,75 94.30% 76 n 0 0 Eagle $ $ Mountai 4,50,00 4,00,00 88.90% 76 n 0 0 Eagle $ $ Mountai 3,10,00 3,00,00 96.80% 76 n 0 0 Eagle $ $ Mountai 3,95,00 3,75,00 94.90% 76 n 0 0 Eagle $ $ Mountai 4,75,00 4,25,25 89.50% 76 n 0 0 Eagle $ $ Mountai 5,00,00 4,25,00 85.00% 76 n 0 0 Eagle $ $ Mountai 4,60,75 4,35,50 94.50% 76 n 0 0 Eagle $ $ Mountai 3,45,00 3,35,00 97.10% 76 n 0 0 Eagle $ $ Mountai 3,75,00 3,30,00 88.00% 76 n 0 0 Eagle $ $ Mountai 4,00,00 3,75,00 93.80% 76 n 0 0 Eagle $ $ Mountai 3,99,00 3,50,00 87.70% 76 n 0 0 Eagle $ Mountai 4,10,00 n 0 $ Lehi 2,59,90 0 $ Lehi 1,10,00 0 $ Lehi 1,65,00 0 $ Lehi 3,00,00 0 $ Lehi 2,15,80 0 $ Lehi 1,80,00 0 $ Lehi 2,50,00 0 $ Lehi 4,50,00 0 $ Lehi 3,25,00 0 Average 94.50% $ 3,50,75 0 $ 2,46,90 5 $ 1,06,00 0 $ 1,56,75 0 $ 2,90,00 0 $ 2,00,00 0 $ 1,75,00 0 $ 2,55,00 0 $ 3,82,50 0 $ 3,02,25 0 80 85.50% 76 95.00% 92 96.40% 82 95.00% 61 96.70% 59 92.70% 54 97.20% 53 ### 48 85.00% 42 93.00% 34