San Diego State University Substance Abuse LGBT People Community Action Plan College Scholarship Fundraiser Table Name Springfield, Jake Harrop, Beth Wash

San Diego State University Substance Abuse LGBT People Community Action Plan College Scholarship Fundraiser Table
Name
Springfield, Jake
Harrop, Beth
Washington, Jon
Ramirez, Alan
Jackson, Elaine
Connelly, Mike
Harris, Robert
Oliver, Ryan
Ricci, Jill
Kennedy, Mary
Curtis, Jeffrey
Hall, Taylor
Brown, David
Regions
North
Midwest
Northeast
Southeast
Status
Full Time
Full Time
Part Time
Full Time
Part Time
Part Time
Full Time
Part Time
Full Time
Full Time
Part Time
Part Time
Full Time
Experience
Hire Date
2
5
3
7
4
1
2
4
2
8
4
6
5
37526
37559
37804
38092
38120
38160
38293
38308
38692
39184
39745
39750
40157
Region
North
Midwest
North
Southeast
Northeast
Midwest
North
Northeast
Southeast
Midwest
Northeast
Midwest
Southeast
Type
Inside
Outside
Outside
Outside
Inside
Outside
Inside
Outside
Inside
Outside
Inside
Outside
Inside
Quota
2112696
2119656
3799318
3168559
3539262
4091220
2130800
2113471
3355143
2122683
1350599
3425945
3916009
YTD Funds Raised
% of Quota
1780578
2074903
3023103
2597820
3867802
3059639
1867427
1693260
2084477
1548949
903908
2996251
3832656
Grade
%of Quota
0%
60%
70%
80%
90%
Grade Table
Grade
F
D
C
B
A
Count
Number
1
2
3
4
5
6
7
8
9
10
10
11
12
13
14
15
16
17
18
19
20
20
21
22
23
24
25
26
27
28
29
30
30
31
32
33
34
35
36
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
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
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
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
Selling Agent
Hernandez
Carey
Goodrich
Lugo
Goodrich
Carey
Lugo
Minkus
Lugo
Minkus
Minkus
Hernandez
Goodrich
Goodrich
Carey
Merkin
Merkin
Minkus
Hernandez
Goodrich
Minkus
Minkus
Hernandez
Lugo
Minkus
Lugo
Goodrich
Lugo
Pijuan
Hernandez
Pijuan
Hernandez
Hernandez
Pijuan
Reuter
Goodrich
Carey
Hernandez
Reuter
List Price
Selling Price
Listing Date Sale Date
725000
645250
4/1/12
6/16/12
350000
340000
4/1/12
5/15/12
418000
400000
4/1/12
6/1/12
215800
200000
4/1/12
5/25/12
385900
385900
4/1/12
4/30/12
500000
465000
4/3/12
6/10/12
300000
290000
4/3/12
6/1/12
565000
535000
4/6/12
9/30/12
219000
215000
4/8/12
6/2/12
165000
156750
4/12/12
6/12/12
165000
156750
4/12/12
6/12/12
325000
320000
4/12/12
5/30/12
750250
700000
4/12/12
11/1/12
110000
106000
4/12/12
7/3/12
314250
304000
4/13/12
8/5/12
555000
500000
4/13/12
10/15/12
450000
382500
4/15/12
6/30/12
345000
339999
4/18/12
6/23/12
300000
300000
4/18/12
4/30/12
425000
415000
4/18/12
6/30/12
325000
308750
4/19/12
7/2/12
325000
308750
4/19/12
7/2/12
325000
302250
4/28/12
6/1/12
325000
320000
4/28/12
5/28/12
285750
300000
4/28/12
6/3/12
425815
400000
4/28/12
9/15/12
250000
232000
4/29/12
9/1/12
515000
485750
4/29/12
8/25/12
400000
375000
4/30/12
6/19/12
450000
400000
4/30/12
10/1/12
310000
300000
4/30/12
7/1/12
250000
255000
5/1/12
6/18/12
250000
255000
5/1/12
6/18/12
375500
375500
5/1/12
5/5/12
185500
179000
5/1/12
6/6/12
395000
375000
5/1/12
7/16/12
410000
397700
5/1/12
6/26/12
560700
550000
5/1/12
5/30/12
450000
400000
5/2/12
9/30/12
37
38
39
40
40
41
42
43
44
45
46
47
48
49
50
50
51
52
53
54
55
56
57
58
59
60
60
61
62
63
64
65
66
67
68
69
70
70
71
72
16235 Orange Boulevard
614 Lincoln Drive
2448 Woodacres Road
401 Pinecone Circle
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
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
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
876 West Hadley Way
1132 SW 52nd Street
416 East Oak
Cedar Hills
Eagle Mountain
Lehi
Alpine
Alpine
Eagle Mountain
Eagle Mountain
American Fork
Alpine
American Fork
Cedar Hills
Eagle Mountain
Alpine
Lehi
Alpine
Alpine
Eagle Mountain
Eagle Mountain
Lehi
Eagle Mountain
American Fork
Cedar Hills
Eagle Mountain
American Fork
Cedar Hills
American Fork
American Fork
Lehi
Eagle Mountain
Eagle Mountain
Eagle Mountain
Alpine
American Fork
Eagle Mountain
Cedar Hills
Alpine
American Fork
American Fork
Cedar Hills
Eagle Mountain
Lugo
Reuter
Hernandez
Goodrich
Goodrich
Pijuan
Minkus
Goodrich
Pijuan
Goodrich
Carey
Merkin
Bethune
Carey
Reuter
Reuter
Bethune
Bethune
Goodrich
Bethune
Carey
Carey
Merkin
Hen
Merkin
Carey
Carey
Bethune
Hen
Carey
Reuter
Minkus
Goodrich
Minkus
Lugo
Minkus
Hernandez
Hernandez
Hernandez
Merkin
395000
475000
450000
1500120
1500120
500000
460750
375000
1250000
365750
650000
325000
147800
180000
310000
310000
345000
375000
215000
450000
315250
475000
400000
316000
289900
345000
345000
259900
399000
410000
285750
189900
335000
250000
275000
589000
345670
345670
254500
300000
380000
425250
382500
1400000
1400000
425000
435500
376000
1225000
355000
598000
325000
150000
175000
291400
291400
335000
330000
195000
400000
300000
450000
375000
316000
279000
330000
330000
246905
350000
350750
300000
186102
330000
275000
264000
575000
345000
345000
236685
250000
5/5/12
5/5/12
5/5/12
5/5/12
5/5/12
5/5/12
5/5/12
5/6/12
5/12/12
5/13/12
5/15/12
5/18/12
5/22/12
5/23/12
5/22/12
5/22/12
5/23/12
5/26/12
5/28/12
5/28/12
5/28/12
5/31/12
5/31/12
5/31/12
5/31/12
6/1/12
6/1/12
6/2/12
6/2/12
6/3/12
6/5/12
6/6/12
6/10/12
6/12/12
6/13/12
6/18/12
6/20/12
6/20/12
6/23/12
6/23/12
7/16/12
9/23/12
6/16/12
9/30/12
9/30/12
11/15/12
11/1/12
5/15/12
6/6/12
8/15/12
6/9/12
5/31/12
6/16/12
7/15/12
6/29/12
6/29/12
6/23/12
8/30/12
6/18/12
8/15/12
8/1/12
8/19/12
7/15/12
6/30/12
6/1/12
8/15/12
8/15/12
9/2/12
11/30/12
10/15/12
7/1/12
7/26/12
9/15/12
6/25/12
7/21/12
7/8/12
9/30/12
9/30/12
9/21/12
11/2/12
73
74
75
76
77
78
79
80
80
81
10 Vestal Drive
425 East Oak
876 West Holiday
103 Jasper Drive
516 East Oak
123 Oak Avenue
600 Ivy Lane
144 Oak Avenue
144 Oak Avenue
1600 Reeves Street
Cedar Hills
Eagle Mountain
Alpine
Lehi
Eagle Mountain
American Fork
Eagle Mountain
American Fork
American Fork
Cedar Hills
Carey
Bethune
Goodrich
Bethune
Pijuan
Carey
Pijuan
Goodrich
Goodrich
Reuter
555000
275900
475000
165900
299999
400000
339999
380500
380500
245900
565000
250000
455000
159264
280000
400000
310000
365000
365000
233605
6/24/12
6/24/12
6/24/12
6/29/12
6/29/12
6/30/12
6/30/12
6/30/12
6/30/12
6/30/12
6/30/12
8/15/12
9/18/12
9/6/12
9/30/12
7/15/12
10/1/12
9/1/12
9/1/12
8/16/12
Number
1
2
3
4
5
6
7
8
9
10
10
11
12
13
14
15
16
17
18
19
20
20
21
22
23
24
25
26
27
28
29
30
30
31
32
33
34
35
36
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
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
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
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
Selling Agent
Hernandez
Carey
Goodrich
Lugo
Goodrich
Carey
Lugo
Minkus
Lugo
Minkus
Minkus
Hernandez
Goodrich
Goodrich
Carey
Merkin
Merkin
Minkus
Hernandez
Goodrich
Minkus
Minkus
Hernandez
Lugo
Minkus
Lugo
Goodrich
Lugo
Pijuan
Hernandez
Pijuan
Hernandez
Hernandez
Pijuan
Reuter
Goodrich
Carey
Hernandez
Reuter
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
List Price
725,000
350,000
418,000
215,800
385,900
500,000
300,000
565,000
219,000
165,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
325,000
285,750
425,815
250,000
515,000
400,000
450,000
310,000
250,000
250,000
375,500
185,500
395,000
410,000
560,700
450,000
Selling Price Listing Date Sale Date
$
645,250
4/1/2012 6/16/2012
$
340,000
4/1/2012 5/15/2012
$
400,000
4/1/2012
6/1/2012
$
200,000
4/1/2012 5/25/2012
$
385,900
4/1/2012 4/30/2012
$
465,000
4/3/2012 6/10/2012
$
290,000
4/3/2012
6/1/2012
$
535,000
4/6/2012 9/30/2012
$
215,000
4/8/2012
6/2/2012
$
156,750
4/12/2012 6/12/2012
$
156,750
4/12/2012 6/12/2012
$
320,000
4/12/2012 5/30/2012
$
700,000
4/12/2012 11/1/2012
$
106,000
4/12/2012
7/3/2012
$
304,000
4/13/2012
8/5/2012
$
500,000
4/13/2012 10/15/2012
$
382,500
4/15/2012 6/30/2012
$
339,999
4/18/2012 6/23/2012
$
300,000
4/18/2012 4/30/2012
$
415,000
4/18/2012 6/30/2012
$
308,750
4/19/2012
7/2/2012
$
308,750
4/19/2012
7/2/2012
$
302,250
4/28/2012
6/1/2012
$
320,000
4/28/2012 5/28/2012
$
300,000
4/28/2012
6/3/2012
$
400,000
4/28/2012 9/15/2012
$
232,000
4/29/2012
9/1/2012
$
485,750
4/29/2012 8/25/2012
$
375,000
4/30/2012 6/19/2012
$
400,000
4/30/2012 10/1/2012
$
300,000
4/30/2012
7/1/2012
$
255,000
5/1/2012 6/18/2012
$
255,000
5/1/2012 6/18/2012
$
375,500
5/1/2012
5/5/2012
$
179,000
5/1/2012
6/6/2012
$
375,000
5/1/2012 7/16/2012
$
397,700
5/1/2012 6/26/2012
$
550,000
5/1/2012 5/30/2012
$
400,000
5/2/2012 9/30/2012
37
38
39
40
40
41
42
43
44
45
46
47
48
49
50
50
51
52
53
54
55
56
57
58
59
60
60
61
62
63
64
65
66
67
68
69
70
70
71
72
16235 Orange Boulevard
614 Lincoln Drive
2448 Woodacres Road
401 Pinecone Circle
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
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
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
876 West Hadley Way
1132 SW 52nd Street
416 East Oak
Cedar Hills
Eagle Mountain
Lehi
Alpine
Alpine
Eagle Mountain
Eagle Mountain
American Fork
Alpine
American Fork
Cedar Hills
Eagle Mountain
Alpine
Lehi
Alpine
Alpine
Eagle Mountain
Eagle Mountain
Lehi
Eagle Mountain
American Fork
Cedar Hills
Eagle Mountain
American Fork
Cedar Hills
American Fork
American Fork
Lehi
Eagle Mountain
Eagle Mountain
Eagle Mountain
Alpine
American Fork
Eagle Mountain
Cedar Hills
Alpine
American Fork
American Fork
Cedar Hills
Eagle Mountain
Lugo
Reuter
Hernandez
Goodrich
Goodrich
Pijuan
Minkus
Goodrich
Pijuan
Goodrich
Carey
Merkin
Bethune
Carey
Reuter
Reuter
Bethune
Bethune
Goodrich
Bethune
Carey
Carey
Merkin
Hen
Merkin
Carey
Carey
Bethune
Hen
Carey
Reuter
Minkus
Goodrich
Minkus
Lugo
Minkus
Hernandez
Hernandez
Hernandez
Merkin
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
395,000
475,000
450,000
1,500,120
1,500,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
400,000
316,000
289,900
345,000
345,000
259,900
399,000
410,000
285,750
189,900
335,000
250,000
275,000
589,000
345,670
345,670
254,500
300,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
175,000
291,400
291,400
335,000
330,000
195,000
400,000
300,000
450,000
375,000
316,000
279,000
330,000
330,000
246,905
350,000
350,750
300,000
186,102
330,000
275,000
264,000
575,000
345,000
345,000
236,685
250,000
5/5/2012 7/16/2012
5/5/2012 9/23/2012
5/5/2012 6/16/2012
5/5/2012 9/30/2012
5/5/2012 9/30/2012
5/5/2012 11/15/2012
5/5/2012 11/1/2012
5/6/2012 5/15/2012
5/12/2012
6/6/2012
5/13/2012 8/15/2012
5/15/2012
6/9/2012
5/18/2012 5/31/2012
5/22/2012 6/16/2012
5/23/2012 7/15/2012
5/22/2012 6/29/2012
5/22/2012 6/29/2012
5/23/2012 6/23/2012
5/26/2012 8/30/2012
5/28/2012 6/18/2012
5/28/2012 8/15/2012
5/28/2012
8/1/2012
5/31/2012 8/19/2012
5/31/2012 7/15/2012
5/31/2012 6/30/2012
5/31/2012
6/1/2012
6/1/2012 8/15/2012
6/1/2012 8/15/2012
6/2/2012
9/2/2012
6/2/2012 11/30/2012
6/3/2012 10/15/2012
6/5/2012
7/1/2012
6/6/2012 7/26/2012
6/10/2012 9/15/2012
6/12/2012 6/25/2012
6/13/2012 7/21/2012
6/18/2012
7/8/2012
6/20/2012 9/30/2012
6/20/2012 9/30/2012
6/23/2012 9/21/2012
6/23/2012 11/2/2012
73
74
75
76
77
78
79
80
80
81
10 Vestal Drive
425 East Oak
876 West Holiday
103 Jasper Drive
516 East Oak
123 Oak Avenue
600 Ivy Lane
144 Oak Avenue
144 Oak Avenue
1600 Reeves Street
Cedar Hills
Eagle Mountain
Alpine
Lehi
Eagle Mountain
American Fork
Eagle Mountain
American Fork
American Fork
Cedar Hills
Carey
Bethune
Goodrich
Bethune
Pijuan
Carey
Pijuan
Goodrich
Goodrich
Reuter
$
$
$
$
$
$
$
$
$
$
555,000
275,900
475,000
165,900
299,999
400,000
339,999
380,500
380,500
245,900
$
$
$
$
$
$
$
$
$
$
565,000
250,000
455,000
159,264
280,000
400,000
310,000
365,000
365,000
233,605
6/24/2012
6/24/2012
6/24/2012
6/29/2012
6/29/2012
6/30/2012
6/30/2012
6/30/2012
6/30/2012
6/30/2012
6/30/2012
8/15/2012
9/18/2012
9/6/2012
9/30/2012
7/15/2012
10/1/2012
9/1/2012
9/1/2012
8/16/2012
Quarterly IT Budget
Q1
Jan
Meeting Room Rent
Copies & Supplies
Travel
Refreshments
Speaker Fees
Miscellaneous
Totals
Feb
350
400
500
125
250
130
Mar
345
360
400
150
300
224
Totals
465
530
625
158
400
325
Quarterly IT Budget
Full Year
Q1
Meeting Room Rent
Copies & Supplies
Travel
Refreshments
Speaker Fees
Miscellaneous
Totals
Q2
Q3
Q4
Totals
Test 3 Prep Assignments
Chapters 4, 5, & 7
College Scholarship Fundraiser Table
Goal
–
Create a table to track fundraising that can be sorted, filtered and queried
Reinforces
• Creating an Excel Table
• Adding computational fields to a table
• Data Validation
• VLOOKUP
• Total Row
• Copy worksheets
• Sorting & Filtering
• Count IF
• PMT
• Professional formatting
• Preparing a worksheet to print on one sheet (scaling, orientation, header/footer)
Worksheet: Table 1
Directions
– You will need to format the data as a table, add a formula to determine the % of Quota
(funds raised/quota), and then use a look up function to determine the grade based on
the % of Quota.
– Count the number of As, Bs, Cs, Ds, and Fs using the appropriate function.
– Add a data validation to the Regions column using the list of regions in A25:A28. Include
an Input Message and an Error Alert to stop the user from inputting anything other than
what is on the drop down list.
– With a table total row, show the total funds raised, the total quota, and the average
years of experience.
– Ensure Table 1 sheet is completed formatted in a professional manner. Make 6 copies
of the Table 1 sheet and rename them as follows performing the tasks required
o Sort by Funds Raised – show the Funds Raised in descending order
o Multilevel Sort – sort by Type (ascending), then by Status (ascending) and then
by Quota (descending)
o Filter by Status – filter for Full Time only
o Multi Filter – show only Inside, full-time and then sort by Funds Raised
(descending)
o Text Filter – show all who are in a region containing the word North then sort
ascending by Region
o Number Filter – show all % of Quotas less than 80% then sort ascending by % of
Quota
pg. 1
Mountain View Realty – Homes
Goal
You have started in internship at Mountain View Realty, a real estate firm that focuses on the
North Utah County area. The previous intern 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 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.
Reinforces
• Creating an Excel Table
• Freeze Panes
• Adding a calculated field to a table
• Adding and working with a Total Row in a table
• Sorting and filtering a table
• Setting up print options for a table including printing titles
• Conditional Formatting
• COUNTIF and IF functions
• Subtotal
Worksheet: – Sales Data and Sub-total
Directions
1. Prepare the large worksheet as a table
a. Freeze the first row on the Sales Data worksheet so the labels will remain visible
as you scroll down through the rows.
b. Convert the data to a table and apply the style of your choice.
c. Remove duplicates. (On the Table Tools ribbon, select Remove Duplicates from
the Tools group, select all, and hit OK.) Eight duplicates should be removed.
2. Add calculated fields and a Total Row
The office manager asked you to insert a column to display the percent of list price. The
formula finds the sale price percentage of the list price. For example, if a house is listed
at $100,000 and sells for $75,000, the percentage of list price is 75%. In some cases, the
percentage is more than 100%. This happens when a bidding war occurs and buyers
increase their offers, which results in the seller getting more than the list price.
a. Insert a new column (or field) to the right of the Selling Price. Name the new
field Percent of List Price.
b. Create a formula to calculate the percent of the list price. (Selling Price / List
Price) Format as a percent with one decimal place.
c. Insert a new column (or field) to the right of the Sale Date. Name the new field
Days on Market.
pg. 2
d. Create a formula to calculate the number of days on the market. (Sale Date –
Listing Date). If necessary, apply the General number format.
e. Add a Total Row to the table. Display the average percent of the list price and
the average number of days on the market.
f. Change the label of the total row from Total to Average
3. Sort and Print the Table
To help the office manager compare house sales by city, you will sort the data. Then,
you will prepare the large table to print.
a. Sort the table by city in alphabetical order, and add a second level to sort by days
on the market with the house on the market the longest at the top within each
city.
b. Adjust the scaling so all columns fit on one page.
c. Set the titles so that the column headings repeat on each page.
d. Change the margins to “Narrow.”
e. Display the table in Page Break Preview. Adjust the page breaks so that there is
a page break at the end of the city American Fo…
Purchase answer to see full
attachment