Appy 7-1 Watson Security Systems
Instructions
Misy XXXXXXXXXXProject 4
Name: BennettAlycia IIII I I IIII IIII
Do Not
Do not insert or delete either rows or columns in any of these worksheets.
Do not move any of the worksheets in this workbook relative to other worksheets.
Do not modify the name and bar code in the grey bar of all worksheets.
Do not change the order of the data if the instructions do not state that.
Do
Before submit your assignment, please rename the file by removing the work "blank" in front of the file name.
For example, if the file titled "blankDoeJohnProj4" must be renamed to "DoeJohnProj4"
Please note that the file name is case-sensitive (i.e., uppercase letter is NOT the same as lowercase letter)
5 % will be deducted for inco
ect FileName, misorder of the worksheet, inco
ect worksheet name, or inco
ect information on the bar code
Complete all of the worksheets.
TableTotal
Name: BennettAlycia IIII I I IIII IIII
Instructions: Based on the data in the worksheet below, convert the data into "Table" and answer the questions below.
Type answers into each cell with appropriate formats (need to be number, not text). No formulas in any cells.
(1) How much has Daniel been paid so far?
(2) What is the total payroll for all drivers so far?
(3) How many miles has Martin driven so far?
(4) How many miles has Travis driven so far?
(5) How many hours has the big truck been driven so far?
(6) What is the total payroll for hazardous material runs?
Company Name Name Hourly Rate Phone Run Date Haz Mat BigTruck Start Time End Time Start Odom End Odom Miles Hours Payroll
This and That Martin $15.00 XXXXXXXXXX 02-Jan-98 No No 3:00:00 AM 5:30:00 AM 45555 45674 119 XXXXXXXXXX $37.50
NimBob Matthew $10.00 XXXXXXXXXX 02-Jan-98 No No 3:45:00 AM 12:15:00 PM 43816 44263 447 XXXXXXXXXX $85.00
Manchesters Furniture Travis $14.00 XXXXXXXXXX 02-Jan-98 No Yes 5:45:00 AM 9:15:00 AM 47908 48061 153 XXXXXXXXXX $49.00
Micron Electronics Kimberly $15.00 XXXXXXXXXX 02-Jan-98 No Yes 11:30:00 AM 4:00:00 PM 46844 47000 156 4.5 $67.50
NimBob Travis $14.00 XXXXXXXXXX 02-Jan-98 Yes Yes 3:30:00 PM 5:00:00 PM 62574 62623 49 1.5 $21.00
Eligios Matthew $10.00 XXXXXXXXXX 03-Jan-98 No Yes 12:30:00 AM 7:45:00 AM 48243 48573 330 XXXXXXXXXX $72.50
Manchesters Furniture Daniel $12.00 XXXXXXXXXX 03-Jan-98 No Yes 10:00:00 AM 11:45:00 AM 59285 59367 82 XXXXXXXXXX $21.00
This and That Kimberly $15.00 XXXXXXXXXX 03-Jan-98 No No 2:45:00 PM 11:00:00 PM 58871 59258 387 8.25 $123.75
Gateway Erin $13.00 XXXXXXXXXX 04-Jan-98 No Yes 4:45:00 AM 1:45:00 PM 61721 62129 408 9 $117.00
Eligios Charles $16.00 XXXXXXXXXX 05-Jan-98 No No 5:00:00 AM 11:30:00 AM 69184 69424 240 XXXXXXXXXX $104.00
Super Cuts Charles $16.00 XXXXXXXXXX 05-Jan-98 No No 6:15:00 AM 3:15:00 PM 41301 41581 280 9 $144.00
Manchesters Furniture Erin $13.00 XXXXXXXXXX 05-Jan-98 No No 11:30:00 AM 7:45:00 PM 48350 48675 325 8.25 $107.25
Apple Corp. Erin $15.00 XXXXXXXXXX 05-Jan-98 Yes No 12:00:00 PM 10:45:00 PM 52493 52872 379 XXXXXXXXXX $161.25
Je
y's Pharmaceuticals Tricia $13.00 XXXXXXXXXX 06-Jan-98 No Yes 5:45:00 AM 3:15:00 PM 62821 63424 603 XXXXXXXXXX $123.50
Manchesters Furniture Staci $11.00 XXXXXXXXXX 06-Jan-98 No No 6:15:00 AM 8:00:00 AM 63600 63666 66 XXXXXXXXXX $19.25
Manchesters Furniture Staci $11.00 XXXXXXXXXX 06-Jan-98 Yes No 7:45:00 AM 11:45:00 AM 43422 43547 125 XXXXXXXXXX $44.00
Micron Electronics Jeanette $16.00 XXXXXXXXXX 06-Jan-98 No No 10:45:00 AM 7:00:00 PM 51566 52000 434 8.25 $132.00
Eligios Jeanette $16.00 XXXXXXXXXX 06-Jan-98 Yes Yes 5:30:00 PM 2:15:00 AM 46135 46580 445 XXXXXXXXXX $140.00
Micron Electronics Tony $15.00 XXXXXXXXXX 07-Jan-98 No Yes 5:30:00 AM 2:45:00 PM 62105 62411 306 XXXXXXXXXX $138.75
Micron Electronics Staci $11.00 XXXXXXXXXX 07-Jan-98 No No 6:30:00 AM 11:30:00 AM 54294 54528 234 XXXXXXXXXX $55.00
Apple Corp. Erin $15.00 XXXXXXXXXX 07-Jan-98 No No 12:00:00 PM 8:15:00 PM 54583 54862 279 8.25 $123.75
Eligios Tricia $13.00 XXXXXXXXXX 07-Jan-98 No Yes 2:45:00 PM 6:30:00 PM 61414 61518 104 3.75 $48.75
Micron Electronics Charles $16.00 XXXXXXXXXX 07-Jan-98 No Yes 3:00:00 PM 6:15:00 PM 68492 68659 167 XXXXXXXXXX $52.00
Eligios Luke $10.00 XXXXXXXXXX 07-Jan-98 No Yes 7:15:00 PM 4:00:00 AM 57967 58396 429 XXXXXXXXXX $87.50
Industrial Light and Magic Matthew $10.00 XXXXXXXXXX 08-Jan-98 No No 11:45:00 AM 5:45:00 PM 40398 40750 352 6 $60.00
Eligios Erin $15.00 XXXXXXXXXX 08-Jan-98 No Yes 12:30:00 PM 7:00:00 PM 62064 62424 360 XXXXXXXXXX $97.50
NimBob Erin $14.00 XXXXXXXXXX 08-Jan-98 No No 12:45:00 PM 2:30:00 PM 58448 58524 76 XXXXXXXXXX $24.50
Eligios Tony $15.00 XXXXXXXXXX 08-Jan-98 No Yes 12:45:00 PM 9:15:00 PM 60825 61216 391 XXXXXXXXXX $127.50
NimBob Erin $15.00 XXXXXXXXXX 08-Jan-98 No No 1:45:00 PM 7:15:00 PM 50455 50654 199 XXXXXXXXXX $82.50
Gateway Tony $15.00 XXXXXXXXXX 08-Jan-98 No Yes 2:15:00 PM 11:45:00 PM 67926 68331 405 XXXXXXXXXX $142.50
Je
y's Pharmaceuticals Tricia $13.00 XXXXXXXXXX 08-Jan-98 Yes Yes 6:00:00 PM 3:15:00 AM 48594 48952 358 XXXXXXXXXX $120.25
Manchesters Furniture Charles $16.00 XXXXXXXXXX 09-Jan-98 Yes No 4:15:00 AM 10:15:00 AM 69993 70284 291 6 $96.00
Eligios Jeanette $16.00 XXXXXXXXXX 09-Jan-98 No Yes 10:15:00 AM 3:45:00 PM 68053 68377 324 XXXXXXXXXX $88.00
Gateway Luke $10.00 XXXXXXXXXX 10-Jan-98 No Yes 4:30:00 AM 2:30:00 PM 43443 43984 541 XXXXXXXXXX $100.00
Je
y's Pharmaceuticals Jeanette $16.00 XXXXXXXXXX 10-Jan-98 No No 5:00:00 AM 3:45:00 PM 67879 68555 676 XXXXXXXXXX $172.00
Industrial Light and Magic Staci $11.00 XXXXXXXXXX 10-Jan-98 No No 8:45:00 AM 8:00:00 PM 43460 44137 677 11.25 $123.75
Super Cuts Daniel $12.00 XXXXXXXXXX 10-Jan-98 No No 11:30:00 AM 9:30:00 PM 43970 44435 465 XXXXXXXXXX $120.00
Je
y's Pharmaceuticals Erin $14.00 XXXXXXXXXX 10-Jan-98 No No 12:30:00 PM 6:00:00 PM 58746 59061 315 XXXXXXXXXX $77.00
This and That Erin $16.00 XXXXXXXXXX 10-Jan-98 No Yes 1:45:00 PM 8:30:00 PM 40375 40662 287 6.75 $108.00
Gateway Staci $11.00 XXXXXXXXXX 10-Jan-98 No No 6:15:00 PM 8:00:00 PM 41347 41428 81 XXXXXXXXXX $19.25
Apple Corp. Luke $10.00 XXXXXXXXXX 13-Jan-98 No No 2:30:00 AM 4:15:00 AM 43200 43261 61 XXXXXXXXXX $17.50
Eligios Tricia $13.00 XXXXXXXXXX 14-Jan-98 No No 5:30:00 AM 9:30:00 AM 40067 40178 111 XXXXXXXXXX $52.00
Apple Corp. Daniel $12.00 XXXXXXXXXX 14-Jan-98 No Yes 6:15:00 AM 8:45:00 AM 48113 48244 131 XXXXXXXXXX $30.00
Je
y's Pharmaceuticals Erin $13.00 XXXXXXXXXX 14-Jan-98 No No 6:30:00 AM 9:00:00 AM 41671 41742 71 XXXXXXXXXX $32.50
This and That Matthew $10.00 XXXXXXXXXX 14-Jan-98 Yes No 9:30:00 AM 12:15:00 PM 48184 48325 141 XXXXXXXXXX $27.50
Manchesters Furniture Travis $14.00 XXXXXXXXXX 14-Jan-98 No No 12:15:00 PM 8:30:00 PM 48531 48841 310 8.25 $115.50
This and That Tricia $13.00 XXXXXXXXXX 15-Jan-98 Yes No 12:30:00 AM 8:45:00 AM 53750 53982 232 8.25 $107.25
Apple Corp. Martin $15.00 XXXXXXXXXX 15-Jan-98 Yes No 6:15:00 AM 3:00:00 PM 67885 68165 280 XXXXXXXXXX $131.25
Super Cuts Charles $16.00 XXXXXXXXXX 15-Jan-98 Yes No 10:00:00 AM 5:45:00 PM 61705 62023 318 XXXXXXXXXX $124.00
Je
y's Pharmaceuticals Tony $15.00 XXXXXXXXXX 16-Jan-98 No No 4:45:00 AM 11:45:00 AM 52481 52792 311 XXXXXXXXXX $105.00
Gateway Charles $16.00 XXXXXXXXXX 16-Jan-98 No No 5:00:00 AM 8:45:00 AM 68330 68427 97 3.75 $60.00
Gateway Charles $16.00 XXXXXXXXXX 16-Jan-98 Yes Yes 6:00:00 AM 10:30:00 AM 67479 67751 272 4.5 $72.00
This and That Staci $11.00 XXXXXXXXXX 16-Jan-98 Yes No 9:00:00 AM 3:00:00 PM 64291 64593 302 6 $66.00
This and That Martin $15.00 XXXXXXXXXX 16-Jan-98 Yes No 9:45:00 AM 6:15:00 PM 47732 48115 383 XXXXXXXXXX $127.50
Eligios Daniel $12.00 XXXXXXXXXX 17-Jan-98 Yes No 7:15:00 AM 4:45:00 PM 59773 60214 441 XXXXXXXXXX $114.00
Eligios Tony $15.00 XXXXXXXXXX 18-Jan-98 No No 7:30:00 AM 11:45:00 AM 69344 69456 112 XXXXXXXXXX $63.75
Industrial Light and Magic Shawn $14.00 XXXXXXXXXX 18-Jan-98 No No 8:45:00 AM 3:45:00 PM 69330 69615 285 XXXXXXXXXX $98.00
This and That Shawn $14.00 XXXXXXXXXX 18-Jan-98 No No 6:30:00 PM 5:30:00 AM 49933 50374 441 XXXXXXXXXX $154.00
Manchesters Furniture Tricia $13.00 XXXXXXXXXX 19-Jan-98 No Yes 4:30:00 AM 6:15:00 AM 61726 61787 61 XXXXXXXXXX $22.75
VLookUp
Employee Product Quantity Sale Date Unit Price Extension/Sales Amount Sales Discount Amount (in Dollar) Date Discount Amount (in Dollar) Discounted Price
Bob Banana 23 Monday
Jill Orange 45 Thursday
Bob Apple 65 Wednesday
George Apple 58 Monday
George Banana 79 Tuesday
Bob Grape 132 Wednesday
Jill Grape 56 Monday
Mike KumQuat 12 Friday
Totals:
Unit Price Vlookup Sales/Extension Discount Vlookup Date Discount Vlookup
Name: BennettAlycia IIII I I IIII IIII
Instructions:
1. Create 3 lookup tables based on the following information:
Unit Price: Apple, $45; Banana, $34; Grape, $89; Kumquat,$150; Orange, $20
Sales Amount Discount: For the sales amount up to less than 1,000, the discount is 0%; for the sales amount from 1,000 up to less than 5,000, the discount is 2%;
For the sales amount from 5,000 up to less than 10,000, the discount is 5%; for the sales amount 10,000 and up, the discount is 8%.
Date Discount: Mon, 10%; Tue, 5%; Wed, 0%; Thu, 2%; Fri, 20%
2. Calculate Unit Price using VLOOKUP function
3. Calculate Extension/Sales Amount, which is equal to Unit Price X Quantity
4. Calculate Sales Amount Discount Amount using VLOOKUP function ((format cell to Cu
ency)
5. Calculate Date Discount Amount using VLOOKUP function (format cell to Cu
ency)
6. Calculate Discounted Price, which is equal to the Extension - (Sales Amount Discount)-(Date Discount Amount)
7. Calculate Total in cells F11 to I11
8. DO NOT change the order of the data from A1 to I12
Format cells in columns G, H, and I in this table to cu
ency.
DO NOT change the order of the data in this area (i.e., A1:I11). Doing so will cause a mis-match in grading.
Apothecary Sales Rep List
Name: BennettAlycia IIII I I IIII IIII
Instructions:
1. Convert the data in Cells A12:J25 into Table
2. Add Total row to show the count of Gender column, the average in Age column, and sums in the Sales and Quota columns as shown above.
3. Use the Advanced Filter function to extract data with criteria (Region=3, Gender=M, and Quota>=3,000,000). Place the criteria under the Criteria Range section and place the results starting on row 30.
Criteria Range
Apothecary Sales Rep List
Region Div Dist Rep Lname Fname Gender Age Sales Quota
2 B 2 101 Knight Tonya F 31 2,167,301 3,000,000
1 A 2 108 Li Chang M 25 4,544,023 5,000,000
3 B 2 120 Law Sam F 58 4,893,014 5,000,000
1 B 2 207 Free Lucy F 32 3,718,292 4,000,000
1 B 2 208 Great Jim M 37 989,483 2,000,000
2 A 3 210 May Rodney M 23 2,565,942 3,000,000
1 B 1 212 Lempke Pat M 56 5,560,345 6,000,000
3 B 2 298 Patel Gandhi M 29 2,109,583 2,000,000
2 B 2 299 Fenner John M 32 3,693,219 5,000,000
3 A 3 312 Penny Josh M 32 2,210,459 3,000,000
1 A 1 313 George Doreen F 35 8,300,845 10,000,000
3 A 1 406 Lopes Pedro M 45 6,921,032 5,000,000
3 A 1 432 Wheatly Jane F 65 1,034,054 2,000,000
Apothecary Sales Rep List
Database Function
Employee Data Criteria
EmpLastname EmpFirstName EmpGender EmpHireDate EmpBasePay EmpGende
Jones Karen F 11-Aug-91 $2,000.00
Payne Kathy F 05-Dec-94 $3,500.00
Morgan Adrian M 25-Oct-93 $6,000.00
Yuan Gail F 01-Mar-97 $5,000.00 Criteria
Payne Justin M 26-Apr-97 $5,000.00 EmpGende
Clarkson Shandra F 30-May-97 $3,000.00
Briones Richard M 28-Apr-98 $4,500.00
Reetz Melinda F 17-Oct-97 $3,000.00
Jones William M 12-May-92 $3,500.00 Criteria
Garza Jeffery M 06-Jan-96 $2,500.00 EmpGender EmpHireDate
Lucio Tanner M 29-Jun-93 $2,500.00
Baldwin Kristina F 21-Mar-98 $2,000.00
Nguyen James M 09-Jul-95 $2,000.00
Bauer Nelda F 08-Aug-98 $3,000.00
Kennemer Mandi F 07-Jul-99 $2,000.00
Name: BennettAlycia IIII I I IIII IIII
Instructions: Using the worksheet above to answer the following questions:
(1) What is the total base pay for women? Using SUMIF function
(2) What is the total of all base pay for men? Using SUMIF function
(3) How many employee make $3500 basepay? Using CountIF function
(4) What is the average basepay for women? Using DAVERAGE function
(5) What is the total basepay for men? Using DSUM function
(6) What is the total basepay for men hired before 1-jan-97? Using DSUM function (syntax for date is <01/01/1997)
All Cells must contain the Database Functions(i.e., typing the number directly into the cells will be graded as inco
ect answers).
This area is for queston 4
This area is for queston 5
This area is for queston 6