Office 2016 – myitlab:grader – Instructions Excel Project YO16_XL_CH05_GRADER_PS1_HW – University…

Office 2016 – myitlab:grader – Instructions Excel Project YO16_XL_CH05_GRADER_PS1_HW – University…Description

Office 2016 – myitlab:grader – Instructions Excel Project
YO16_XL_CH05_GRADER_PS1_HW – University Call Center 1.3
Project Description: The University’s call center needs a workbook developed that will use data to analyze the performance of the call center. The call center contacted your professor and she recommended you for the task. The workbook will set up a variety of tools that will help assess the efficiency of the center and its staff. Your will develop a workbook for the call center manager that will help with the center’s data analysis.
Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Open the downloaded Excel file named e03ch05_grader_h1_CallCenter.xlsx. Save the file with the name e03_grader_h1_CallCenter_LastFirst, using your last and first name. 0.000 2 On the Lists worksheet, create the following named ranges to use within formulas: A2:A6 as Department A14:B18 as GradeScale B9:H9 as DayofWeek Select the range E1:I5, and create named ranges using the top row as the range values. 4.000 3 Select the CallData worksheet. In cell A9, format the data as an Excel table with headers using the current data set. Apply White, Table Style Light 1. In cell H9, type Issue as the field label. In cell I9, type Grade as the field label. In cell J9, type Weekday as the field label. Note, depending upon the Office version used, the table style name may be Table Style Light 1. 9.000 4 On the CallData worksheet, select range A9:J128, and then name the entire data set, including the labels, as CallDataAll. Next, create named ranges using the top row as the range values. 6.000 5 In cell H10, enter an INDEX function that will use a nested INDIRECT reference to the Dept named range listed in column C (C10), and use the Reason field in column B (B10) 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. 4.000 6 In cell I10, enter a VLOOKUP function that will convert the Satisfaction Rating to a grade found in the second column of the GradeScale named range. The formula is not looking for an exact match. 4.000 7 In cell J10, enter 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 width as needed. 4.000 8 On the CallData worksheet: 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. 2.000 9 On the CallCenterReport worksheet, add formulas that will summarize the issues for the department entered in cell B3. In cell B6, add an INDEX function that will use an INDIRECT function to retrieve the department issue list for the department listed in cell B3. Use an absolute reference to B3, and then use a relative cell reference to A6 as the row_num argument. Copy the formula down to cell B9. 4.000 10 In cell C6, add a COUNTIFS function that will count the number of departments on the CallData worksheet. Use the Dept named range as criteria_range1, and then use an absolute reference to B3 as criteria1. Use the Reason named range as criteria_range2, and then use cell A6 as criteria2. Copy the formula down to cell C9. 4.000 11 In cell D6, add a COUNTIFS function that will count the number of calls coming from the department listed in B3. Use the Dept named range as criteria_range1, and then use an absolute reference to B3 as criteria1. Use the Reason named range as criteria_range2, and then use cell A6 as criteria2. Use the On_Hold named range as criteria_range3, and then use “Y” as criteria3. Copy the formula down to cell D9. 4.000 12 In cell E6, add an IF statement with a nested AND that will enter a status notice. If the number of calls on issue in cell C6 is greater than 3 and the number of calls on hold in cell D6 is greater than 2, then “Check Hold Issue” should display. Otherwise, nothing should display. Copy the formula down to cell E9. 4.000 13 In cell B12, add an AVERAGEIF function that will find the average call length for the Dept named range and the criteria specified in cell A12. In cell C12, add a COUNTIF function that will count the number of calls for the Dept named range and the criteria specified in cell A12. 8.000 14 In cell D12, add a formula that sums two COUNTIFS formulas. The first COUNTIFS will count the number of calls associated with the Dept named range and criteria specified in cell A12 that received a grade F, and the second COUNTIFS will do the same for grade D. 4.000 15 In cell E12, add an IF statement using a nested OR function that will return any notes associated with the issue. If there are more than 10 issues reported with a grade below a C in cell D12, or when the number of scores less than C divided by the total calls in cell C12 is greater than 50%, then “Explore Issues” should display. Otherwise, the result will be blank. Copy the formulas in B12:E12 down through row B16:E16. 4.000 16 In cell H11, add a MAX function that will show the maximum call length minutes using the Call_Length named range. In cell H12, add an INDEX function that will use the Dept named range as the array associated with a MATCH function to determine the lookup array for the longest call referenced in H11 from the Call_Length named range with an exact match. 8.000 17 In cell H13, add an INDEX function that will pull the satisfaction rating associated with a MATCH function to determine the lookup array for the longest call referenced in H11 from the Call_Length range with an exact match. 4.000 18 On the CallCenterReport worksheet, in cell G19, type Y. In cell J19, type Friday. Name the range A18:J19 Call_Criteria. In cell B22, add a DCOUNT function for the CallDataAll database to find the count of the satisfaction rating currently listed in cell B21 using the Call_Criteria named range. 7.000 19 In B23:B26, add database functions that find the DAVERAGE (B23), DSUM (B24), DMAX (B25), and DMIN (B26) for the CallDataAll named range. Use named ranges in the formulas. Select the cell range B22:B26, and then copy the formulas to column C. 16.000 20 Save the workbook, exit Excel, and then submit your file as directed by your instructor. 0.000 Total Points 100.000
Updated: 10/17/2017 1 Current_Instruction.docx

li_e03ch05_grader_h1_CallCenter.xlsx

CallData

Call HourReasonDeptCall LengthSatisfaction RatingCall DayOn HoldIssueGradeWeekday
Call HourReasonDeptCall LengthSatisfaction RatingCall DayOn Hold
82Accounting64.54Y
161Public_Affairs6.73.75Y
152Accounting5.55.91N
31Public_Affairs3.79.22N
211Admissions6.55.91N
153Public_Affairs8.863N
221Human_Resources6.36.44N
122Admissions27.22Y
193Accounting5.47.66N
151Financial_Aid5.94.25Y
191Public_Affairs3.39.23N
142Accounting4.95.13N
124Public_Affairs45.95N
202Public_Affairs4.38.22N
142Public_Affairs5.36.75N
112Public_Affairs5.16.85N
151Admissions3.56.52Y
161Admissions5.48.46N
102Public_Affairs3.66.85Y
152Admissions7.24.27Y
12Public_Affairs3.39.97N
93Admissions6.38.36N
132Public_Affairs2.66.54Y
203Admissions7.26.65N
172Admissions1.87.52N
111Admissions2.58.31N
142Admissions4.76.62N
173Public_Affairs7.65.33N
211Financial_Aid5.75.47Y
161Public_Affairs36.75Y
21Admissions3.79.44N
131Accounting1.57.81N
113Public_Affairs3.39.31N
183Admissions4.45.45Y
202Accounting541Y
232Public_Affairs3.283Y
81Public_Affairs4.54.95Y
82Admissions8.14.14Y
51Human_Resources6.56.24N
211Public_Affairs5.78.56N
202Admissions4.55.67N
193Admissions4.892N
41Admissions5.98.24N
222Admissions4.44.57Y
201Public_Affairs3.29.47N
203Admissions43.83Y
164Financial_Aid4.96.52N
111Public_Affairs5.76.25Y
72Public_Affairs59.95N
122Public_Affairs4.68.84N
161Financial_Aid3.58.31N
202Public_Affairs5.77.11N
144Admissions46.12N
121Admissions3.19.65N
62Admissions3.5107N
172Admissions2.78.11N
212Human_Resources557Y
64Admissions2.77.73N
202Public_Affairs6.16.76N
201Admissions64.41Y
222Public_Affairs4.78.13N
31Public_Affairs5.354Y
91Admissions3.48.96N
132Accounting4.96.32Y
231Accounting5.57.23N
242Admissions4.57.13N
103Admissions7.33.86Y
204Human_Resources4.56.51N
93Human_Resources5.84.92Y
142Accounting4.27.63N
111Human_Resources3.8105N
182Public_Affairs4.58.12N
202Admissions3.5102N
62Accounting1.49.54N
62Admissions3.8102N
131Accounting4.56.12N
211Accounting4.66.83N
231Admissions4.56.65Y
101Admissions2.67.34Y
191Public_Affairs6.37.71N
133Admissions1.38.84N
182Human_Resources4.95.52N
182Human_Resources4.85.46Y
122Accounting3.981N
192Accounting6.86.64Y
111Public_Affairs6.28.77N
44Human_Resources56.22N
243Human_Resources2.48.16N
212Accounting6.864N
123Human_Resources3.18.36N
142Human_Resources3.27.41N
143Admissions7.86.91N
93Admissions5.44.82N
142Public_Affairs6.65.86Y
123Admissions3.58.65Y
193Human_Resources5.77.23N
41Public_Affairs6.36.55N
83Admissions8.366N
72Human_Resources47.26N
12Human_Resources4.87.74N
111Accounting3.19.86N
152Accounting54.63Y
101Public_Affairs5.58.95N
121Admissions5.257Y
92Public_Affairs4.68.91N
53Admissions2.27.31Y
204Accounting6.17.86N
241Accounting4.48.34N
203Admissions64.75Y
93Financial_Aid3.9107N
103Accounting5.27.22N
142Accounting4.65.53N
224Accounting6.58.33N
153Financial_Aid7.15.11Y
24Financial_Aid6.36.67N
101Admissions4.58.54N
21Admissions7.46.45N
183Financial_Aid5.46.95N
231Financial_Aid5.352Y

&F

Lists

DepartmentPublic AffairsHuman ResourcesAdmissionsAccountingFinancial Aid
Public Affairs1Frozen screenBlackboardBannerBlackboardInternet
Human Resources2VoicemailElectricalIntranetInternetBlackboard
Admissions3OtherInternetBlackboardIntranet
Accounting4IntranetInternet
Financial Aid
Day of WeekSundayMondayTuesdayWednesdayThursdayFridaySaturday
Financial Aid
Satisfaction RatingGrade
0F
4D
6C
8B
9A+

&F

CallCenterReport

Call Center Report
DepartmentFinancial_Aid
Your Office: Public_Affairs Human_Resources Admissions Accounting Financial_Aid Student_Life
Issues# Calls on Issue# On HoldStatus
1
2
3
4
DepartmentAverage Call LengthTotal CallsBelow CNotesLongest Call
Public_AffairsDepartment
Human_ResourcesScore
Admissions
Accounting
Financial_Aid
Call HourReasonDeptCall LengthSatisfaction RatingCall DayOn HoldIssueGradeWeekday
Satisfaction RatingCall Length
Count
Average
Sum
Max
Min

&FEstimated Deadline (in hours)