now i just need help figuring out the amount billed
FS VLOOKLIPIDS, Rates A$1:0511,3,FALSE) Gain 049 Customer 1041 ned Date 17 13 wy Total S 10 OS EL SER OF SP SE 111701 GET route werkiration IS STM 7 1 Het Talatta 250 115 w NE 2/ Averter Sharofanogen RO 3 SE 10 250 LUWE LURE 2 3 graphie Monowe der DW Trip Deliler Do yes Owner Dream SS 65 S TOWE LUME LUVE TOWE SE 10/01 1/14/01 11 2100 How The ME 10.00 $100.00 E 16 SE 3 M M2 LOVE PHCM PRODE VOO ORD 15 SS ES S 150 150 5. 1 . and Me Town 1200 150 40 40 18 40 10 45 55 3/41 2/11 12/21 M20 1 10 6 $ VE 10.7 POLI 64 P041 942 FOR 10 11 iss vanie 12 PODE 13 0044 14 a 153 100 17054 30 PCB 19 DS CO2 21 22 2 20 24 25 24 ROBOT 27 COO 20 COOL 29 pool 30 PUTO COSA ROY ROXY ACT H00 2 ? + $ 5 LUKE LIVE TUDE DY Sri Site Pree 100 Net 7 Wow 30 Other Support 4 Network STI 2 V 7 View 1 1 Hardware Support DOWY Sweet 10 h 1 New Tree ST Security camera 15 1000 12.30 TE SCO 4 M 2221 2/250 321 M MINI 5 D 56 SE SE VE $ DE OP Gwaran wer Grawerve Horcycle Job Sende MIR Pena PAR WC WCY Therapy Theophy Wilmington Wilton WA Wind 1721 3/12 ww 102 2 1 5 175 7:25 5.00 135 $ TURN TU LUE UE 9 WE $ 5 3 9 15 40 15 15 TE 09 TE Wie TR0 UTC w V1 2/17/21 21 22471 . WA 2 3/321 M142 mi 291 W/ 2.00 625 10.7 6.00 $ 5 16 Dicowy 5 MA 3/14 2 2 500 10 Microsoft Excel March Customers Fatos HH Home Insert Draw Page Layout Formulas Data Review Calibri (Body) 11 A A Paste BIU Tag ||| A2 B C D Hourly Rate $ 35 $ 30 $ 40 s 45 $ 55 $ 35 $ 35 5 65 $ 50 5 40 CallTypeID Description 1 l Hardware Support 2 Software Support 3 Network Troubleshooting 4 Network Installation 5 Training 6 Security Camera Maintenance 7 Virus Removal 8 Disaster Recovery 10 9 VoIP Service 11 10 Other 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 March Hours Customers Faites Ready cel File Edit View Insert Format Tools Data Window Help AutoSave BESU. e02r1NCCTS (1) Home Insert Draw Page Layout Formulas Data Review View Tell me Calibri (Body) 11 A A General Y Paste MI > I> 3 $ % , Al Ji CustomerlD A D Address 163 Bannister Drive 268 Mendocino Circle 63 McRae Road 367 Meadow Road 848 Brookridge Circle 110 Robin Drive 466 Cedrus Circle 256 Gorsuch Street 101 lacque Circle 109 Icefall Drive 564 Pilgrim Drive 897 Ronny Place 360 icefall Drive 1068 South Tug Road 259 Roger Graves Road 986 Colusa Circle 218 Snug Harbor Circle 990 Geese Circle 549 Main Street 319 Koine Circle 141 Snowshoe Lane 1128 Gum Street Customer Company Name 2 PC001 SVC Pharmacy P0004 Monday's Restaurant 4 POOO7 Penny's Arcade 5 PCOS The Home Store 6 PC012 Grahame Tax Service 7 PC016 Supermercado 8PC017 Graber Dentistry 9 PC018 The Trophy Factory 10 PCO20 Wild Birs Office Superstore 11 PC023 City Movie Superplex 12 PC027 Wilmington Motor Vehicles 13 PC028 Cuddyer & Tejada Law Office 14 PC029 JobSource 15 PC031 Homer's Cyber Cafe 16 PC035 Fountains of Odessa 17 PCO38 Dr. Jennifer Lynn 18 PC040 Fabrics and More 19 PC041 Beta graphics 20 PCO-44 Dr. Lorenzo Reyes 21 PCO45 Yelbir Credit Union 22 PC048 Blue Moon Day Spa 23 PCOS Zapatos 24 25 26 27 28 29 30 31 32 33 City Newark Newark Wilmington Wilmington Newark Wilmington Wilmington Wilmington Wilmington Delaware City Wilmington New Castle Newark Newark Odessa Wilmington Wilmington Newark Wilmington Wilmington Wilmington Wilmington State DE DE DE DE DE DE DE DE DE DE DE DE DE DE DE DE DE DE DE DE DE DE ap 19718 19711 19802 19802 19702 19801 19894 19802 19802 19706 15802 19721 19718 Phone 351-490-9677 857-610-2424 327-729-5558 711-826-1561 950-660-7078 300-272-8352 959-373-6764 300-255-8297 958-759-8665 998-368-2385 959-730-6633 833-310-1435 976-517-8180 327-292-6210 959-7814850 327-580-9607 333-330-8520 8.30-525-3278 600-723-7818 424-334-1571 BS7-338-6470 833-932-2594 19712 19730 19802 19802 19714 19802 19802 19802 19802 34 35 36 37 March Hours Customers Rates + Ready astle County Technical Services New Castle County Technical Services (NCCTS) provides technical support services for a num- ber of companies in New Castle County, Delaware. You previously downloaded a dataset from the company's database that contains a list of call cases that were closed during March, formatted the worksheet, and calculated the number of days each case was open and the amount owed per transaction. Since then, you added two worksheets, one for your customer list and one for the rates. In the March Hours worksheet, you inserted new columns to look up customer names and rates from the respective worksheets. You want to use this data to enter summary statistics to complete billing analysis for March. a. Open e02r1NCCTS and save it as e02r1NCCTS_LastFirst. b. Insert a VLOOKUP function in cell C5, to return the customer name based on the customer ID in column B and the lookup table in the Customers worksheet. C. Copy the function from cell C5 to the range C6:C36. d. Insert a VLOOKUP function in cell F5 to look up Rates for CallTypeID in column D using the lookup table in the Rates worksheet. Copy the function from cell F5 to the range F6:F36. e. Insert an IF function in cell K5 to calculate the amount billed. If the hours logged is less than or equal to 10(cell 012), multiply the rate by the hours worked. Otherwise multiply the rate by the hours worked and add a $100 premium (cell 013) to the bill. Copy the function from cell K5 to the range K6:K36. f. Insert a function in cell 05 to calculate the total hours logged in column]. g. Insert a function in cell 06 to calculate the total amount billed in column K. h. Insert a function in cell 07 to calculate the average days required to complete a service request (column 1). i. Insert a function in cell 08 to calculate the fewest days open in column l. 1. Insert a function in cell 09 to calculate the most days open in column I. k. Insert a function in cell E2 to add the current date and time to the worksheet. 1. Insert a footer with your name on the left side, the sheet name in the center and the file name code on the right side of the worksheet. Return to Normal view. m. Save and close the workbook. Based on your instructor's directions, submit cO2r1NCCTS_LastFirst. Paste B1 Edising $%% 33-3 3 Cell Styles Format F5 =VLOOKUP(D5, Rates A$1:$11,3,FALSE) A B C D Ant ted Opened Date 273721 1/1171 2015 Ced Den 1/11/21 10 3/11/21 3/10/1 7 3/4 5 31 4 noun red 12.50 2.50 1. 2.50 450 Smart Total Houten Total Amounted AweDyd oper Sheratore Longest Days Open 12 2/27/21 9.75 2 wamen Hour Threshold Premium line 10 $ 100 2 3/8/21 3/14/21 1/11/21 3/21/21 1/15 3,2121 2032 1/12/21 C D Customer Customer 5649 PC041aphics 6 064 POUM achie CO P2041 Merapie PO tet 047 PO4 Moen Day Spa 10 056 PCOM Quddar & Tonic 11 055 POD Difer 12 1961 PO3 Dr. Jennifer Lynn 13 OCO PC044 Dr. Lore Reyes 14 0 PCD40 Dr. Lee Here 15. 1063 P0044 Dr. Love 16 069 PC044 Dr. Lorenzo Reyes 17 054 P040 Fabrics and More 10 050 PCOS Fountains of 1939 PODS Fountain of Odessa 20 065 PO Grabe 21053 PC017 Graber Dentistry 22 646 PC012 Grahame Service 23 044 PCOS Homercybercafe 24 051 PC029 JobSource 25 058 P0004 Monday's Restaurant 2602 C007 Penny Arcade 27073 PODOZ Penny Made 20 052 PC001 SC Pharmacy 29 30 PODO: SVC Pharmacy 30 652 PCOES The Trophy Factory 31 59 PCOM The Trophy Factory 12040 PC027 Wilmington Motor Vehicle 33 06 PCO2 Wilmington Motor 14 041 P077 Wing Mote While 35072 PC027 Wilmington Motor vehide 36 062 PCOS Zapatos 37 30 GeTypeID Chi The Name Newer troubleshooting a Networking 6 Security Camera Mainance 2 Vinstmovil 1 Hanware support Network rooting String Die cowy Sing ST 9 WP Service 10. Other 3 Network Troubleshooting 7 Virus 10 Other 2 Softwwe support 4 Network intation 5 Training 7 Virus Remove 7 removal 1 Hardware Support 1 Hardware support Der Recovery 2 Software Support 10 Other 1 Hardware Support Network Troubleshooting Training Security camera Maintenance Der Recovery Disaster Recovery 7 rumowa s 40 15 45 S 19 15 15 $35 S40 5 55 $ 6 S 55 S SS $ SO $ 40 :$ 40 $35 $ 40 $ 30 $ 5 55 15 35 S 35 5 15 15 35 $ 65 5 10 S 40 15 15 $ 40 SS 5 35 15 65 $ 65 $ 25 9 8 3 10 . 1 5 6 2/6/21 M21 1/21 3/8/21 1/19/21 3/12/21 22/21 31 1491 2/21/21 3/12/21 1921 2/28/21 2521 3421 3/8/21 3/17/21 3/26/21 W221 2792 21 32 2/22/21 314/21 2/21/21 3/29/21 3/12/21 1/21/21 21 1/5/21 3/1/21 3/12/21 1/10/21 3/19/21 3/3121 1921 3221 110/21 1721 2/321 1/24/21 3/6/21 1/11/21 2/14/21 8 2 2 S S 11 1 9 9 9.25 625 225 1.50 1.50 5.25 1025 12.00 5.35 10.75 11.50 10.00 12.50 8.00 1.75 725 5.00 1.35 3.75 2.00 635 10.15 600 1.00 500 10.15 10 11 2 2
