Excel

2

>Sales Data

8

Kenwood Road

,000

5,2

/12

2

West Oak Circle

50,000

/12

3

North Choctaw

American Fork

5,000

/

/12

7

4

70 Pinellas Road

,000

5

Minkus

5 1971 Glenview Road Lehi Minkus $ 165,000 $ 156,750 4/12/12 6/12/12 61

6

Alpine

4/12/12

3

7

3 North Mountain View

American Fork Carey

8

Alpine

76

9

Alpine Minkus

/12

Alpine Minkus

10 4081 Lybyer Avenue Alpine Minkus $ 325,000 $ 308,750 4/19/12 7/2/12 74

11

Lugo $ 325,000

30

12

Eagle Mountain Minkus

4/28/12

13

Eagle Mountain Lugo

5,750

/12

14

Alpine

50

15

Eagle Mountain Hernandez $ 450,000 $ 400,000 4/30/12

16

Lehi Hernandez

48

16 8307 S Indian River Drive Lehi Hernandez $ 250,000 $ 255,000 5/1/12 6/18/12 48

17

Eagle Mountain Pijuan

$ 375,500 5/1/12

4

18

Cedar Hills

,000

5/1/12

36

19

Cedar Hills Carey

5/1/12

56

20

Cedar Hills Hernandez

5/1/12

29

21

American Fork Reuter $ 450,000 $ 400,000

								Number		Address		City		Selling Agent		List Price		Selling Price		Listing Date		Sale Date		Days on Market
	1			8		6		7								Cedar Hills									Hernandez	$ 72			5	$ 6				4		50		4/1/ Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper		12	6/			16				76
		11								American Fork						Carey	$		3		$ 340,000		4/1/12	5/		15		44
3		21												Minkus	$		56		$ 535,000		4/6/12			9		30			17
		13													Alpine						Lugo	$ 2		19		$ 215,000		4/8/12		6/2/12		55
		1971 Glenview Road						Lehi			$ 165,000			$ 156,750					4/12/12			6/12/12			61
	575 South Choctaw		Goodrich		$ 750,250		$ 700,000		11/1/12			20
		14		$ 314,250		$ 304,000		4/13/12		8/5/12		114
	8030 Steeplechase Drive		Merkin						$ 450,000		$ 382,500		4/15/12		6/30/12
	2006 Cutwater Court		$ 345,000		$ 339,999	4/		18		6/23/12		66
		10			4081 Lybyer Avenue					$ 325,000			$ 308,750			4/19/12			7/2/12			74
	9000 South Sunset Avenue										Eagle Mountain		$ 320,000				4/28/12		5/28/12
	8432 South Sunset Avenue		$ 285,750		$ 300,000		6/3/12				36
	240 East Jefferson Way		$ 515,000	$			48	4/		29		8/25/12		118
	224 Rockaway Street				Pijuan						$ 400,000		$ 375,000				4/30/12		6/19/12
	260 East Jefferson Way		10/1/12		154
		8307 S Indian River Drive			$ 250,000			$ 255,000											5/1/12			6/18/12
	123 Ivy Lane				$ 375,500		5/5/12
	9408 Forest Hills Circle				Reuter		$ 185,500		$ 1	79		6/6/12
	11971 SW 269 Terrace		$ 410,000		$ 397,700		6/26/12
	1857 Pine Drive		$ 560,700		$ 550,000		5/30/12
	3461 East Lindon Way		5/2/12			9/30/12		151

Filtered Data

Number Address City Selling Agent List Price Selling Price

Listing Date Sale Date Days on Market

6 575 South Choctaw Alpine Goodrich $ 750,250 $ 700,000

4/12/12 11/1/12

8 8030 Steeplechase Drive Alpine Merkin $ 450,000 $ 382,500

4/15/12 6/30/12 76

10 4081 Lybyer Avenue Alpine Minkus $ 325,000 $ 308,750

4/19/12 7/2/12 74

9 2006 Cutwater Court Alpine Minkus $ 345,000 $ 339,999

6/23/12 66

4

Alpine Lugo

$ 215,000

4/8/12 6/2/12 55

14 224 Rockaway Street Alpine Pijuan $ 400,000 $ 375,000

4/30/12 6/19/12 50

3

American Fork Minkus

$ 535,000

4/6/12 9/30/12

21 3461 East Lindon Way American Fork Reuter $ 450,000 $ 400,000

5/2/12 9/30/12 151

7

American Fork Carey $ 314,250 $ 304,000

4/13/12 8/5/12 114

2

American Fork Carey

$ 340,000

4/1/12

44

1

Cedar Hills Hernandez

4/1/12

76

19 11971 SW 269 Terrace Cedar Hills Carey $ 410,000 $ 397,700

5/1/12 6/26/12 56

18 9408 Forest Hills Circle Cedar Hills Reuter $ 185,500 $ 179,000

5/1/12 6/6/12 36

20 1857 Pine Drive Cedar Hills Hernandez $ 560,700 $ 550,000

5/1/12 5/30/12 29

15 260 East Jefferson Way Eagle Mountain Hernandez $ 450,000 $ 400,000 88.9% 4/30/12 10/1/12 154
13 240 East Jefferson Way Eagle Mountain Lugo $ 515,000

8/25/12 118

12 8432 South Sunset Avenue Eagle Mountain Minkus $ 285,750 $ 300,000

4/28/12 6/3/12 36

11 9000 South Sunset Avenue Eagle Mountain Lugo $ 325,000 $ 320,000

4/28/12 5/28/12 30

17 123 Ivy Lane Eagle Mountain Pijuan $ 375,500 $ 375,500

5/1/12 5/5/12 4

5 1971 Glenview Road Lehi Minkus $ 165,000 $ 156,750 95.0% 4/12/12 6/12/12 61
16 8307 S Indian River Drive Lehi Hernandez $ 250,000 $ 255,000

5/1/12 6/18/12 48

79

Percent of List Price
93.3%	203
85.0%
	95.0%
98.6%	4/18/12
1370 Pinellas Road	$ 219,000	98.2%
93.8%
321 North Choctaw	$ 565,000	94.7%	177
	88.9%
143 North Mountain View	96.7%
11 West Oak Circle	$ 350,000	97.1%	5/15/12
8687 Kenwood Road	$ 725,000	$ 645,250	89.0%	6/16/12
97.0%
96.5%
98.1%
$ 485,750	94.3%	4/29/12
105.0%
98.5%
100.0%
102.0%
Averages	95.5%

Office 2010 – myitlab:grader – Instructions Exploring! Excel Ch. 04 – EOCM Project

Mountain View Realty

Project Description:
You just got an internship at Mountain View Realty, a real estate firm that focuses in the North Utah County area. The previous intern developed a spreadsheet listing houses listed and sold during the last several months. She included addresses, location, list price, selling price, listing date, and date sold. You need to convert the data to a table and manipulate the 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. Download and open the file named Exploring_e04_Grader_EOC.xlsx. 0
2 Freeze the first row (column headers) on the Sales Data worksheet. 3
3 Convert the data in the range A1:I25 to a table, and then apply the Table Style Medium 17. 6
4 Remove duplicate records from the table. 4
5 Insert a column to the right of the Selling Price field. Name the new field Percent of List Price and adjust the width of that column to 22.00 (159 pixels). 4
6 Create a formula with structured references in cell G2 to calculate the percent of the list price (that is, the selling price divided by the list price). 8
7 Format the Percent of List Price column (G2:G22) with the Percent number type with one decimal place. 4
8 Add a total row to display the average percent of list price and average number of days on market. Format the average number of days on market as a whole number. Change the label for the total row to Averages. 13
9 Sort the table in alphabetical order by the City column. Add a second sorting level to sort the days on market for each city from largest to smallest. 12
10 Format the Sales Data worksheet so that the column headings will show on all pages. 5
11 Insert a page break above row 12 on the Sales Data worksheet. Remove the vertical page break. Be sure to switch back to Normal view, if necessary. 9
12 On the Filtered Data worksheet, filter the data to display properties in the cities of Alpine, Cedar Hills, and Eagle Mountain (column C). 6
13 Filter the data to display records for houses that were on the market for 30 days or more. 6
14 Apply the 3 Arrows (Colored) icon set to the days on market values (J2:J19). 4
15 Apply the Gradient Fill Light Blue Data Bar conditional formatting to the selling prices (F2:F19). 4
16 Create a new conditional format rule that applies Yellow fill (fourth column, bottom row) and bold font to values that are greater than or equal to 98% in the Percent of List Price column (G2:G19). 12
17 Ensure that the worksheets are correctly named and placed in the following order in the workbook: Sales Data, Filtered Data. Save and close the workbook, and submit based on your instructor’s directions. 0
Total Points 100

Updated on: 4/5/2010 1 E_CH04_EXPV1_EOCM _Instructions x