Computer HW Exel
Dream Maker
e_ch04_expv1_h1_3.zipE_CH04_EXPV1_H1_Instructions.docx
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 Instructions Points Possible 1 Start Excel. Open the downloaded file named exploring_e04_grader_h1.xlsx. 0 2 In the Sales Data worksheet, freeze the top row. 5 3 In the Sales Data worksheet, convert the data to a table and apply Table Style Medium 17. 6 4 In the Sales Data worksheet, remove duplicate records in the table. 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. 6 6 In the Sales Data worksheet, create a formula in cell E2 with structured references to calculate the percent of the list price. Format the field with Percent Style with one decimal place in the range E2:E43. 6 7 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. 6 8 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 as a whole number. Type Averages in cell A44. 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. 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. 6 11 In the Sales Data worksheet, repeat the field names on all pages. 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. 5 13 Display the Filtered Data worksheet. Convert the table to a range of data. 5 14 Filter the data to display the cities of Alpine, Cedar Hills, and Eagle Mountain. 6 15 Filter the data 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 values. 5 17 Apply the Light Blue Data Bar conditional formatting in the Gradient Fill section to the selling prices. 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. 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 100
Updated: 04/04/2013 1 E_CH04_EXPV1_H1_Instructions.docx
exploring_e04_grader_h1.xlsx
Sales Data
| Address | City | List Price | Selling Price | Listing Date | Sale Date |
| 8687 Kenwood Road | Cedar Hills | $ 725,000 | $ 645,250 | 4/1/16 | 6/16/16 |
| 11 West Oak Circle | American Fork | $ 350,000 | $ 340,000 | 4/1/16 | 5/15/16 |
| 614 West Cedar Drive | Lehi | $ 215,800 | $ 200,000 | 4/1/16 | 5/25/16 |
| 750 South Apple Way | American Fork | $ 385,900 | $ 385,900 | 4/1/16 | 4/30/16 |
| 7 Kingston Court | Alpine | $ 500,000 | $ 465,000 | 4/3/16 | 6/10/16 |
| 321 North Choctaw | American Fork | $ 565,000 | $ 535,000 | 4/6/16 | 9/30/16 |
| 1370 Pinellas Road | Alpine | $ 219,000 | $ 215,000 | 4/8/16 | 6/2/16 |
| 1971 Glenview Road | Lehi | $ 165,000 | $ 156,750 | 4/12/16 | 6/12/16 |
| 575 South Choctaw | Alpine | $ 750,250 | $ 700,000 | 4/12/16 | 11/1/16 |
| 143 North Mountain View | American Fork | $ 314,250 | $ 304,000 | 4/13/16 | 8/5/16 |
| 29 East Oak Circle | American Fork | $ 555,000 | $ 500,000 | 4/13/16 | 10/15/16 |
| 8030 Steeplechase Drive | Alpine | $ 450,000 | $ 382,500 | 4/15/16 | 6/30/16 |
| 2006 Cutwater Court | Alpine | $ 345,000 | $ 339,999 | 4/18/16 | 6/23/16 |
| 9876 South Sunset Avenue | Eagle Mountain | $ 300,000 | $ 300,000 | 4/18/16 | 4/30/16 |
| 321 West Walnut Grove | American Fork | $ 425,000 | $ 415,000 | 4/18/16 | 6/30/16 |
| 4081 Lybyer Avenue | Alpine | $ 325,000 | $ 308,750 | 4/19/16 | 7/2/16 |
| 1900 Glenview Road | Lehi | $ 325,000 | $ 302,250 | 4/28/16 | 6/1/16 |
| 9000 South Sunset Avenue | Eagle Mountain | $ 325,000 | $ 320,000 | 4/28/16 | 5/28/16 |
| 8432 South Sunset Avenue | Eagle Mountain | $ 285,750 | $ 300,000 | 4/28/16 | 6/3/16 |
| 831 South Weber | American Fork | $ 425,815 | $ 400,000 | 4/28/16 | 9/15/16 |
| 714 Timp View Lane | American Fork | $ 250,000 | $ 232,000 | 4/29/16 | 9/1/16 |
| 224 Rockaway Street | Alpine | $ 400,000 | $ 375,000 | 4/30/16 | 6/19/16 |
| 260 East Jefferson Way | Eagle Mountain | $ 450,000 | $ 400,000 | 4/30/16 | 10/1/16 |
| 260 East Jefferson Way | Eagle Mountain | $ 450,000 | $ 400,000 | 4/30/16 | 10/1/16 |
| 8307 S Indian River Drive | Lehi | $ 250,000 | $ 255,000 | 5/1/16 | 6/18/16 |
| 123 Ivy Lane | Eagle Mountain | $ 375,500 | $ 375,500 | 5/1/16 | 5/5/16 |
| 245 Ivy Lane | Eagle Mountain | $ 395,000 | $ 375,000 | 5/1/16 | 7/16/16 |
| 11971 SW 269 Terrace | Cedar Hills | $ 410,000 | $ 397,700 | 5/1/16 | 6/26/16 |
| 1857 Pine Drive | Cedar Hills | $ 560,700 | $ 550,000 | 5/1/16 | 5/30/16 |
| 3461 East Lindon Way | American Fork | $ 450,000 | $ 400,000 | 5/2/16 | 9/30/16 |
| 16235 Orange Boulevard | Cedar Hills | $ 395,000 | $ 380,000 | 5/5/16 | 7/16/16 |
| 614 Lincoln Drive | Eagle Mountain | $ 475,000 | $ 425,250 | 5/5/16 | 9/23/16 |
| 421 Ivy Lane | Eagle Mountain | $ 500,000 | $ 425,000 | 5/5/16 | 11/15/16 |
| 765 East Sheridan Lane | Eagle Mountain | $ 460,750 | $ 435,500 | 5/5/16 | 11/1/16 |
| 1414 N Sheridan Road | Alpine | $ 1,250,000 | $ 1,225,000 | 5/12/16 | 6/6/16 |
| 487 Blue Skies Drive | American Fork | $ 365,750 | $ 355,000 | 5/13/16 | 8/15/16 |
| 10700 Lake Shore Lane | Cedar Hills | $ 650,000 | $ 598,000 | 5/15/16 | 6/9/16 |
| 73 East Oak Street | Eagle Mountain | $ 325,000 | $ 325,000 | 5/18/16 | 5/31/16 |
| 73 East Oak Street | Eagle Mountain | $ 325,000 | $ 325,000 | 5/18/16 | 5/31/16 |
| 3412 Kilmer Street | Lehi | $ 180,000 | $ 175,000 | 5/23/16 | 7/15/16 |
| 77 East Oak Street | Eagle Mountain | $ 345,000 | $ 335,000 | 5/23/16 | 6/23/16 |
| 1629 NW 43rd Street | Cedar Hills | $ 475,000 | $ 450,000 | 5/31/16 | 8/19/16 |
| 3400 North Sunset Lane | Eagle Mountain | $ 400,000 | $ 375,000 | 5/31/16 | 7/15/16 |
| 876 South California Way | American Fork | $ 316,000 | $ 316,000 | 5/31/16 | 6/30/16 |
Filtered Data
| City | List Price | Selling Price | Percent of List Price | Days on Market |
| Cedar Hills | $ 725,000 | $ 645,250 | 89.0% | 76 |
| American Fork | $ 350,000 | $ 340,000 | 97.1% | 44 |
| American Fork | $ 418,000 | $ 400,000 | 95.7% | 61 |
| Lehi | $ 215,800 | $ 200,000 | 92.7% | 54 |
| American Fork | $ 385,900 | $ 385,900 | 100.0% | 29 |
| Alpine | $ 500,000 | $ 465,000 | 93.0% | 68 |
| Lehi | $ 300,000 | $ 290,000 | 96.7% | 59 |
| American Fork | $ 565,000 | $ 535,000 | 94.7% | 177 |
| Alpine | $ 219,000 | $ 215,000 | 98.2% | 55 |
| Lehi | $ 165,000 | $ 156,750 | 95.0% | 61 |
| American Fork | $ 325,000 | $ 320,000 | 98.5% | 48 |
| Alpine | $ 750,250 | $ 700,000 | 93.3% | 203 |
| Lehi | $ 110,000 | $ 106,000 | 96.4% | 82 |
| American Fork | $ 314,250 | $ 304,000 | 96.7% | 114 |
| American Fork | $ 555,000 | $ 500,000 | 90.1% | 185 |
| Alpine | $ 450,000 | $ 382,500 | 85.0% | 76 |
| Alpine | $ 345,000 | $ 339,999 | 98.6% | 66 |
| Eagle Mountain | $ 300,000 | $ 300,000 | 100.0% | 12 |
| American Fork | $ 425,000 | $ 415,000 | 97.6% | 73 |
| Alpine | $ 325,000 | $ 308,750 | 95.0% | 74 |
| Lehi | $ 325,000 | $ 302,250 | 93.0% | 34 |
| Eagle Mountain | $ 325,000 | $ 320,000 | 98.5% | 30 |
| Eagle Mountain | $ 285,750 | $ 300,000 | 105.0% | 36 |
| American Fork | $ 425,815 | $ 400,000 | 93.9% | 140 |
| American Fork | $ 250,000 | $ 232,000 | 92.8% | 125 |
| Eagle Mountain | $ 515,000 | $ 485,750 | 94.3% | 118 |
| Alpine | $ 400,000 | $ 375,000 | 93.8% | 50 |
| Eagle Mountain | $ 450,000 | $ 400,000 | 88.9% | 154 |
| Eagle Mountain | $ 310,000 | $ 300,000 | 96.8% | 62 |
| Lehi | $ 250,000 | $ 255,000 | 102.0% | 48 |
| Eagle Mountain | $ 375,500 | $ 375,500 | 100.0% | 4 |
| Cedar Hills | $ 185,500 | $ 179,000 | 96.5% | 36 |
| Eagle Mountain | $ 395,000 | $ 375,000 | 94.9% | 76 |
| Cedar Hills | $ 410,000 | $ 397,700 | 97.0% | 56 |
| Cedar Hills | $ 560,700 | $ 550,000 | 98.1% | 29 |
| American Fork | $ 450,000 | $ 400,000 | 88.9% | 151 |
| Cedar Hills | $ 395,000 | $ 380,000 | 96.2% | 72 |
| Eagle Mountain | $ 475,000 | $ 425,250 | 89.5% | 141 |
| Lehi | $ 450,000 | $ 382,500 | 85.0% | 42 |
| Alpine | $ 1,500,120 | $ 1,400,000 | 93.3% | 148 |
| Eagle Mountain | $ 500,000 | $ 425,000 | 85.0% | 194 |
| Eagle Mountain | $ 460,750 | $ 435,500 | 94.5% | 180 |
| American Fork | $ 375,000 | $ 376,000 | 100.3% | 9 |
| Alpine | $ 1,250,000 | $ 1,225,000 | 98.0% | 25 |
| American Fork | $ 365,750 | $ 355,000 | 97.1% | 94 |
| Cedar Hills | $ 650,000 | $ 598,000 | 92.0% | 25 |
| Eagle Mountain | $ 325,000 | $ 325,000 | 100.0% | 13 |
| Alpine | $ 147,800 | $ 150,000 | 101.5% | 25 |
| Lehi | $ 180,000 | $ 175,000 | 97.2% | 53 |
| Alpine | $ 310,000 | $ 291,400 | 94.0% | 38 |
| Eagle Mountain | $ 345,000 | $ 335,000 | 97.1% | 31 |
| Eagle Mountain | $ 375,000 | $ 330,000 | 88.0% | 96 |
| Lehi | $ 215,000 | $ 195,000 | 90.7% | 21 |
| Eagle Mountain | $ 450,000 | $ 400,000 | 88.9% | 79 |
| American Fork | $ 315,250 | $ 300,000 | 95.2% | 65 |
| Cedar Hills | $ 475,000 | $ 450,000 | 94.7% | 80 |
| Eagle Mountain | $ 400,000 | $ 375,000 | 93.8% | 45 |
| American Fork | $ 316,000 | $ 316,000 | 100.0% | 30 |
| Cedar Hills | $ 289,900 | $ 279,000 | 96.2% | 1 |
| American Fork | $ 345,000 | $ 330,000 | 95.7% | 75 |
| Lehi | $ 259,900 | $ 246,905 | 95.0% | 92 |
| Eagle Mountain | $ 399,000 | $ 350,000 | 87.7% | 181 |
| Eagle Mountain | $ 410,000 | $ 350,750 | 85.5% | 134 |
| Eagle Mountain | $ 285,750 | $ 300,000 | 105.0% | 26 |
| Alpine | $ 189,900 | $ 186,102 | 98.0% | 50 |
| American Fork | $ 335,000 | $ 330,000 | 98.5% | 97 |
| Eagle Mountain | $ 250,000 | $ 275,000 | 110.0% | 13 |
| Cedar Hills | $ 275,000 | $ 264,000 | 96.0% | 38 |
| Alpine | $ 589,000 | $ 575,000 | 97.6% | 20 |
| American Fork | $ 345,670 | $ 345,000 | 99.8% | 102 |
