YOF EWS05 A1 - Helping the University Call Center 1.1
The University’s call center needs a spreadsheet developed that will use call data to analyze the performance of the center. It contacted your professor and she recommended you for the task. The spreadsheet will set up a variety of tools that will help assess the efficiency of the center and its staff. Your job is to develop the tools for the call center manager that will help with his assessment.
For the purpose of grading the project you are required to perform the following tasks:
Start Excel. Open the downloaded Excel file namede03ws05_grader_a1_start.xlsx. Save the file with the namee03ws05CallCenter_LastFirst replacing LastFirst with your name.
Click the Lists worksheet tab. Name the range A2:A6 asDepartment. Name the range A14:B18 as GradeScale. Name the range B9:H9 as DayofWeek. Select the range E1:I5, and create named ranges using the top row as the range values.
Click the Call Data worksheet tab. In cell A7, add an Excel table with headers using the current data set. In cell H7, addIssue as the field label. In cell I7, add Grade as the field label. In cell J7, add Weekday as the field label.
Select range A7:J22, and then name the entire data set, including the labels, as CallDataAll. Create named ranges using the top row as the range values.In cell H8, add an INDEX function that will use a nested INDIRECT reference to the Dept named range listed in column C, and use the Reason field in column B as the row number to return for the department name in the referenced named range. Nest the function inside an IF function so that issues currently displaying as a 0 will display as a blank cell. Resize the column width as needed.
In cell I8, add a VLOOKUP function that will convert the Satisfaction Score to a grade found in the second column of the GradeScale named range. In cell J8, add an INDEX function that will convert the Call Day to the actual weekday found in row 1 of the DayofWeek named range. Resize the column widths as needed.
On the Call Data worksheet, create an advanced filter. In cell C2, type Public_Affairs. In cell G2, type Y. Run an advanced filter on the table data set using the criteria range A1:J2.
In cell D4, add a SUBTOTAL function that will count the number of call hour cells returned for the subset of records that have been filtered. In cell D5, add a SUBTOTAL function that will determine the average call length for the subset of records that have been filtered.