Hamilton Exploring Worksheet No copying and pasting formulas … Directions and excel spread sheet uploaded Grader – Instructions Excel 2016 Project EX16_X

Hamilton Exploring Worksheet No copying and pasting formulas … Directions and excel spread sheet uploaded Grader – Instructions
Excel 2016 Project
EX16_XL_CH05_GRADER_ML1_HW – Mountain View Realty 1.3
Project Description:
Steps to Perform:
Step
Instructions
Points
Possible
1
Open exploring_e05_grader_h2_RealEstate.xlsx and save it as
exploring_e05_grader_h2_RealEstate_LastFirst.
0
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
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.
5
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).
9
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.
15
6
Format the average days on market as numbers with zero decimal places. Set a print area for
the range C1:J88.
3
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
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.
10
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.
9
10
Add a filter to cell B3 to display only Alpine and Cedar Hills.
2
11
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.
3
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.
10
Created On: 06/25/2018
1
EX16_XL_CH05_GRADER_ML1_HW – Mountain View Realty 1.3
Grader – Instructions
Excel 2016 Project
Points
Possible
Step
Instructions
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.
5
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.
6
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
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
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
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
Total Points
Created On: 06/25/2018
2
100
EX16_XL_CH05_GRADER_ML1_HW – Mountain View Realty 1.3
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
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
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
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
$
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
$
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
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
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
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
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
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
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
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
400,000
300,000
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
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
Days on
Market
5/28/2018 8/15/2018
5/28/2018
8/1/2018
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

Purchase answer to see full
attachment

"Order a similar paper and get 100% plagiarism free, professional written paper now!"

Order Now