New Castle County Technical Services New Castle County Technical Services (NCCTS) provides technical support services for a number of companies in New Castle County, Delaware You previously downloaded a dataset from the companys 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 Open e02r1NCCTS and save it as e02r1NCCTS LastFirst 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 Copy the function from cell C5 to the range C6 C36 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 Insert an IF function in cell K5 to calculate the amount billed If the hours logged is less than or equal to 10 (cell O12), multiply the rate by the hours worked Otherwise multiply the rate by the hours worked and add a $100 premium (cell O13) to the bill Copy the function from cell K5 to the range K6 K36 Insert a function in cell O5 to calculate the total hours logged in column J Insert a function in cell O6 to calculate the total amount billed in column K Insert a function in cell O7 to calculate the average days required to complete a service request (column I) Insert a function in cell O8 to calculate the fewest days open in column I Insert a function in cell O9 to calculate the most days open in column I Insert a function in cell E2 to add the current date and time to the worksheet 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 Save and close the workbook Name the file e02r1NCCTS LastFirst Upload it in the Excel Exercise assignment box on eLearn

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 25, 2024

New Castle County Technical Services New Castle County Technical Services (NCCTS) provides technical support services for a number of companies in New Castle County, Delaware.

New Castle County Technical Services

New Castle County Technical Services (NCCTS) provides technical support services for a number of companies in New Castle County, Delaware. You previously downloaded a dataset from the companys 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.

Open e02r1NCCTS and save it as e02r1NCCTS_LastFirst.
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.
Copy the function from cell C5 to the range C6:C36.
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.
Insert an IF function in cell K5 to calculate the amount billed. If the hours logged is less than or equal to 10 (cell O12), multiply the rate by the hours worked. Otherwise multiply the rate by the hours worked and add a $100 premium (cell O13) to the bill. Copy the function from cell K5 to the range K6:K36.
Insert a function in cell O5 to calculate the total hours logged in column J.
Insert a function in cell O6 to calculate the total amount billed in column K.
Insert a function in cell O7 to calculate the average days required to complete a service request (column I).
Insert a function in cell O8 to calculate the fewest days open in column I.
Insert a function in cell O9 to calculate the most days open in column I.
Insert a function in cell E2 to add the current date and time to the worksheet.
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.
Save and close the workbook. Name the file e02r1NCCTS_LastFirst. Upload it in the Excel Exercise assignment box on eLearn.