I am having trouble understanding where to grab my information from with the vlookup formula for tab 2. I included all the tabs from my worksheet because I'm so lost. An explanation would be grately appreciated. Thanks!
$285.00 (1) How much has Daniel been paid so far? 15,154.50 [2) What is the total payroll for all drivers so far? 782 (3) How many miles has Martin driven so far? 512 (4) How many miles has Travis driven so far? 135.5 (5) How many hours has the big truck been driven so far? $1,352.00 [6) What is the total payroll for hazardous material runs? Company Name | Nam . Hourly Ray Phony Run Day Haz M . BigTruc. Start Tin. End Tim . Start Odo + End Odo. Mile:. Hour . Payrc . This and That Martin $15.00 888-1315 02-Jan-98 No No 3:00:00 AM 5:30:00 AM 45555 45674 119 2.5 $37.50 NimBob Matthew $10.00 650-5519 02-Jan-98 No 3:45:00 AM 12:15:00 PM 43816 44263 147 8.5 $85.00 Manchesters $14.00 650-2881 02-Jan-98 No Yes 5:45:00 AM 9:15:00 AM 47908 48061 153 3.5 $49.00 Micron Electronics Kimberly $15.00 650 02-Jan-98 No Yes 11:30:00 AM 4:00:00 PM 4684 7000 156 4.5 $67.50 NimBob Travis $14.00 650-2881 02-Jan-98 Yes Yes 3:30:00 PM 5:00:00 PM 2574 62623 49 1.5 $21.00 Eligio: Matthew $10.00 650-5519 03-Jan-98 No Yes 12:30:00 AM 7:45:00 AM 48243 48573 330 1.25 $72.50 Manchesters Daniel $12.00 992- 13-Jan-98 No Yes 10:00:00 AM 11:45:00 AM 19285 9367 82 1.75 $21.00 This and That Kimberly $15.00 650- 03-Jan-98 No No 2:45:00 PM 11:00:00 PM 58871 59258 387 8.25 $123.75 Gateway Erin $13.00 756-8381 04-Jan-98 No ies 4:45:00 AM 1:45:00 PM 61721 62129 408 9 $117.00 Eliglos Charles $16.00 842-7108 05-Jan-98 No No 5:00:00 AM 11:30:00 AM 69184 69424 240 6.5 $104.00 Super Cuts Charles $16.00 855- 05-Jan-98 NO No 6:15:00 AM 3:15:00 PM 41301 4158 280 9 $144.00 Manchesters Erin $13.00 756-8381 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 855-4751 05-Jan-98 Yes No 12:00:00 PM 10:45:00 PM 52493 52872 379 10.75 $161.25 Jerry's Tricia $13.00 756- 06-Jan-98 No : 5:45:00 AM 3:15:00 PM 62821 63424 603 9.5 $123.50 Manchesters Stac $11.00 756-8117 06-Jan-98 No 6:15:00 AM 8:00:00 AM 63600 63666 1.75 $19.25 Manchesters Staci $11.00 756-8117 06-Jan-98 Yes No 7:45:00 AM 11:45:00 AM 43422 43547 125 4 $44.00 Micron Electronics Jeanette $16.00 992- 06-Jan-98 No No 10:45:00 AM 7:00:00 PM 51566 52000 134 8.25 $132.00 Eligios Jeanette $16.00 992- 06-Jan-98 Yes res 5:30:00 PM 2:15:00 AM 46135 46580 445 8.75 $140.00 Micron Electronics Tony $15.00 994- 07-Jan-98 No Yes 5:30:00 AM 2:45:00 PM :2105 62411 306 9.25 $138.75 Micron Electronics Stac $11.00 756-8117 07-Jan-98 No No 6:30:00 AM 11:30:00 AM 54294 54528 234 5 $55.00 Apple Corp. Erin $15.00 678- 07-Jan-98 No No 12:00:00 PM 8:15:00 PM 458 54862 279 8.25 $123.75 ligios Tricia $13.00 756- 07-Jan-98 No les 2:45:00 PM 6:30:00 PM 61414 61518 104 3.75 $48.75 Micron Electronics Charles $16.00 842-7108 07-Jan-98 No Yes 3:00:00 PM 6:15:00 PM 8492 8659 167 3.25 $52.00 Eligios Luke $10.00 855- 07-Jan-98 NO Yes 7:15:00 PM 4:00:00 AM 57967 18396 129 8.75 $87.50 ndustrial Light and Matthew $10.00 650-5519 08-Jan-98 No No 11:45:00 AM 5:45:00 PM 40398 40750 352 6 $60.00 Eligios Erin $15.00 673- 08-Jan-98 No Yes 12:30:00 PM 7:00:00 PM 2064 62424 360 6.5 $97.50 NimBot Erin $14.00 756-3201 08-Jan-98 No No 12:45:00 PM 2:30:00 PM 58448 58524 76 1.75 $24.50 Eliglos Tony $15.00 994 08-Jan-98 No Yes 12:45:00 PM 9:15:00 PM 60825 61216 391 8.5 $127.50 NimBob Erin $15.00 678- 08-Jan-98 No No 1:45:00 PM 7:15:00 PM 50455 50654 199 5.5 $82.50 Gateway Tony $15.00 994. 18-Jan-98 No Yes 2:15:00 PM 11:45:00 PM $7921 6833' 105 9.5 $142.50 Jerry's Tricia $13.00 756- 08-Jan-98 Yes Yes 6:00:00 PM 3:15:00 AM 48594 8952 358 9.25 $120.25 Manchesters Charles $16.00 842-7108 09-Jan-98 Yes No 4:15:00 AM 10:15:00 AM 69993 70284 291 6 $96.00 Eligios Jeanette $16.00 992 09-Jan-98 No Yes 10:15:00 AM 3:45:00 PM 68053 68377 324 5.5 $88.00 heMeleE Luke $10.00 85 10-Jan-98 No Yes 4:30:00 AM 2:30:00 PM 43443 43984 541 10 $100.00 Jerry's Jeanette $16.00 992- 0-Jan-98 NO No 5:00:00 AM 3:45:00 PM $7879 18555 676 10.75 $172.00 Industrial Light and Stac $11.00 756-8117 0-Jan-98 No No 8:45:00 AM 8:00:00 PM 43460 44137 677 11.25 $123.75 Super Cuts Daniel $12.00 992- 10-Jan-98 No No 11:30:00 AM 9:30:00 PM 43970 14435 465 10 $120.00 Jerry's Erin $14.00 756-3201 U-Jan-98 No No 12:30:00 PM 6:00:00 PM 5874 59061 315 5.5 $77.00 This and That Erin $16.00 756- 10-Jan-98 No Yes 1:45:00 PM 8:30:00 PM 40375 10662 6.75 $108.00 Stac $11.00 756-8117 10-Jan-98 No 6:15:00 PM 8:00:00 PM 41347 41428 81 Gateway 1.75 $19.25 Luke $10.00 855- No No 2:30:00 AM 4:15:00 AM 43200 1.75 Apple Corp. 3-Jan-98 43261 $17.50 111 ligIos Tricia $13.00 756 14-Jan-98 Na No 5:30:00 AM 9:30:00 AM 40067 40178 4 $52.00 Apple Corp Daniel $12.00 992- 14-Jan-98 No Yes 6:15:00 AM 8:45:00 AM 48713 48244 31 2.5 $30.00 Jerry's Erin $13.00 756-8381 14-Jan-98 No No 6:30:00 AM 9:00:00 AM 41671 41742 71 2.5 $32.50 This and That Matthew $10.00 650-5519 14-Jan-98 No 9:30:00 AM 12:15:00 PM 48184 48325 41 2.75 $27.50 Manchesters Travis $14.00 650-2881 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 756- 5-Jan-98 YES No 12:30:00 AM 8:45:00 AM 53750 13982 232 8.25 $107.25 Apple Corp. Martin $15.00 888-1315 5-Jan-98 Yes No 6:15:00 AM 3:00:00 PM 67885 68165 280 8.75 $131.25 Super Cuts Charles $16.00 842-7108 15-Jan-98 Yes No 10:00:00 AM 5:45:00 PM 61705 2023 318 7.75 $124.00 Jerry's Tony $15.00 994- 16-Jan-98 No No 4:45:00 AM 11:45:00 AM 52481 52792 311 7 $105.00 Gateway Charles $16.00 855- 16-Jan-98 No No 5:00:00 AM 8:45:00 AM 68330 68427 3.75 $60.00 heMeleg Charles $16.00 842-7108 16-Jan-98 Yes Yes 6:00:00 AM 10:30:00 AM 6747 67751 272 4.5 $72.00 This and That Staci $11.00 756-8117 6-Jan-98 Yes No 9:00:00 AM 3:00:00 PM 64291 :4593 302 $66.00 This and That Martin $15.00 888-1315 6-Jan-98 No 9:45:00 AM 6:15:00 PM 1773 48115 383 8.5 $127.50 Eligios Daniel $12.00 992- 7-Jan-98 Yes No 7:15:00 AM 4:45:00 PM 59773 60214 441 9.5 $114.00 Eligios Tony $15.00 994- 18-Jan-98 No No 7:30:00 AM 11:45:00 AM 69344 69458 112 4.25 $63.75 Industrial Light and Shawn $14.00 994- 3-Jan-98 No No 8:45:00 AM 3:45:00 PM 39330 69615 285 7 $98.00 This and That Shawn $14.00 994- 8-Jan-98 No No 6:30:00 PM 5:30:00 AM 49933 50374 441 $154.00 Manchesters Tricia $13.00 756- 19-Jan-98 No Yes 4:30:00 AM 6:15:00 AM 61726 1787 61 1.75 $22.75A C D E F G H K Extension/Sales Sales Discount Amount | Date Discount Amount Employee Product Quantity Sale Date Unit Price Amount (in Dollar) (in Dollar) Discounted Price 2 Bob Banana 23 Monday 3 Jill Orange 45 Thursday 4 Bob Apple 65 Wednesday 5 George Apple 58 Monday 6 George Banana 79 Tuesday DO NOT change the order of Bob Grape 132 Wednesday the data in this area (i.e. 8 Jill Grape 56 Monday A1:111). Doing so will cause a 9 Mike KumQuat 12 Friday mis-match in grading. 10 11 Totals: 12 Sales/Extension Discount 13 Unit Price Vlookup Vlookup Date Discount Vlookup Format cells in columns G, H, 14 and I in this table to currency. 15 16 17 18 19 20 Name: AmreinCrystal III II III IIIIII 21 Instructions: 22 1. Create 3 lookup tables based on the following information: 23 Unit Price: Apple, $45; Banana, $34; Grape, $89; Kumquat,$150; Orange, $20 24 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%; 25 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%. 26 Date Discount: Mon, 10%; Tue, 5%; Wed, 0%; Thu, 2%; Fri, 20% 27 2. Calculate Unit Price using VLOOKUP function 28 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 Currency) 5. Calculate Date Discount Amount using VLOOKUP function (format cell to Currency) 31 6. Calculate Discounted Price, which is equal to the Extension - (Sales Amount Discount)-(Date Discount Amount) 32 7. Calculate Total in cells F11 to 111 33 8. DO NOT change the order of the data from A1 to 112\fEmployee Data Criteria This area is for queston 4 EmpLastname EmpFirstName EmpGender EmpHireDate EmpBasePay EmpGender 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 EmpGender This area is for queston 5 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 This area is for queston 6 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: AmreinCrystal III II III IIIIII 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
