Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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.

image text in transcribedimage text in transcribedimage text in transcribed

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

I am stuck from step9

Can anyone help me to complete this from 9??

RY TX Call Hour Reason Call Length Satisfaction Rating Call Day On Hold Issue Grade Week Dept Pablic Affairs call Hou Reaso - Call Lengt Call Da On Hol Grade Satisfaction Ratin 4.5 3.7 5.9 5.5 5 Y IN 2 N mo Issue Internet D Frozen screen F Internet D Frozen screen A+ Banner D Other Blackboard C Intranet 6 7.2 7.6 3 N 4 N 2 Y 6N 5 Y 3N 3 N 4.2 - 9.2 Internet Frozen screen A+ Internet - 5.1 5 N - - 5.1 3.5 Dept 2 Accounting 1 Public Affairs 2 Accounting 1 Public Affairs 1 Admissions 3 Public Affairs 1 Human Resources 2 Admissions 3 Accounting 1 Financial Aid 1 Public Affairs 2 Accounting 4 Public Affairs 2 Public Affairs 2 Public Affairs 2 Public Affairs 1 Admissions 1 Admissions 2 Public Affairs 2 Admissions 2 Public Affairs 3 Admissions 2 Public Affairs 3 Admissions 2 Admissions 1 Admissions 2 Admissions 3 Public Affairs 1 Financial_Aid 1 Public Affairs 1 Admissions 1 Accounting 3 Public Affairs 3 Admissions 2 Accounting 2 Public Affairs - 5.4 6 N Week Wednesd Thursday Sunday Monday Sunday Tuesday Wednesd Monday Friday Thursday Tuesday Tuesday Thursday Monday Thursday Thursday Monday Friday Thursday Saturday Saturday Friday Wednesd Thursday Monday Sunday Monday Tuesday Saturday Thursday Wednesd Sunday Sunday Thursday Sunday Tuesday 5 Y - 7 N - 6 N 6.3 2.6 - 1.8 Voicemail B Voicemail Voicemail C Banner Banner B Voicemail c Intranet D Voicemail A+ Blackboard B Voicemail C Blackboard C Intranet Banner Intranet Other Internet Frozen screen C Banner Blackboard C Other A+ Blackboard D Internet D Voicemail B 4 Y 5 N 2 N 1 N 2 N 3 N - - 7 Y - IN 1 N 5 Y Oo 3.2 1 W CallCenter Report + CallData Lists Filter Mode Ready 1 Department 2 Public Affairs 3 Human Resources 4 Admissions 5 Accounting 6 Financial Aid Public Affairs 1 Frozen screen 2 Voicemail 3 Other 4 Human Resources Blackboard Electrical Internet Intranet Admissions Banner Intranet Blackboard Accounting Blackboard Internet Financial Aid Internet Blackboard Intranet Internet Sunday Monday Tuesday Wednesday Thursday Friday Saturday ! 9 Day of Week 10 Financial Aid Satisfaction Rating Grade OF 4D 6C Call Center Report Department Financial Aid #Calls on Issue #On Hold Status Issues 6 1 72 #REF! 10 Average Call Length Total Calls Below Notes Longest Call Department Score 11 Department 12 Public Affairs 13 Human Resources 14 Admissions 15 Accounting 16 Financial Aid 18 Call Hour Reason Dept Call Length Satisfaction Rating Call Day On Hold Issue Grade Weekday Satisfaction Rating Call Length 22 Count 23 Average 24 Sum 25 Max 26 Min RY TX Call Hour Reason Call Length Satisfaction Rating Call Day On Hold Issue Grade Week Dept Pablic Affairs call Hou Reaso - Call Lengt Call Da On Hol Grade Satisfaction Ratin 4.5 3.7 5.9 5.5 5 Y IN 2 N mo Issue Internet D Frozen screen F Internet D Frozen screen A+ Banner D Other Blackboard C Intranet 6 7.2 7.6 3 N 4 N 2 Y 6N 5 Y 3N 3 N 4.2 - 9.2 Internet Frozen screen A+ Internet - 5.1 5 N - - 5.1 3.5 Dept 2 Accounting 1 Public Affairs 2 Accounting 1 Public Affairs 1 Admissions 3 Public Affairs 1 Human Resources 2 Admissions 3 Accounting 1 Financial Aid 1 Public Affairs 2 Accounting 4 Public Affairs 2 Public Affairs 2 Public Affairs 2 Public Affairs 1 Admissions 1 Admissions 2 Public Affairs 2 Admissions 2 Public Affairs 3 Admissions 2 Public Affairs 3 Admissions 2 Admissions 1 Admissions 2 Admissions 3 Public Affairs 1 Financial_Aid 1 Public Affairs 1 Admissions 1 Accounting 3 Public Affairs 3 Admissions 2 Accounting 2 Public Affairs - 5.4 6 N Week Wednesd Thursday Sunday Monday Sunday Tuesday Wednesd Monday Friday Thursday Tuesday Tuesday Thursday Monday Thursday Thursday Monday Friday Thursday Saturday Saturday Friday Wednesd Thursday Monday Sunday Monday Tuesday Saturday Thursday Wednesd Sunday Sunday Thursday Sunday Tuesday 5 Y - 7 N - 6 N 6.3 2.6 - 1.8 Voicemail B Voicemail Voicemail C Banner Banner B Voicemail c Intranet D Voicemail A+ Blackboard B Voicemail C Blackboard C Intranet Banner Intranet Other Internet Frozen screen C Banner Blackboard C Other A+ Blackboard D Internet D Voicemail B 4 Y 5 N 2 N 1 N 2 N 3 N - - 7 Y - IN 1 N 5 Y Oo 3.2 1 W CallCenter Report + CallData Lists Filter Mode Ready 1 Department 2 Public Affairs 3 Human Resources 4 Admissions 5 Accounting 6 Financial Aid Public Affairs 1 Frozen screen 2 Voicemail 3 Other 4 Human Resources Blackboard Electrical Internet Intranet Admissions Banner Intranet Blackboard Accounting Blackboard Internet Financial Aid Internet Blackboard Intranet Internet Sunday Monday Tuesday Wednesday Thursday Friday Saturday ! 9 Day of Week 10 Financial Aid Satisfaction Rating Grade OF 4D 6C Call Center Report Department Financial Aid #Calls on Issue #On Hold Status Issues 6 1 72 #REF! 10 Average Call Length Total Calls Below Notes Longest Call Department Score 11 Department 12 Public Affairs 13 Human Resources 14 Admissions 15 Accounting 16 Financial Aid 18 Call Hour Reason Dept Call Length Satisfaction Rating Call Day On Hold Issue Grade Weekday Satisfaction Rating Call Length 22 Count 23 Average 24 Sum 25 Max 26 Min

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Beginning ASP.NET 4.5 Databases

Authors: Sandeep Chanda, Damien Foggon

3rd Edition

1430243805, 978-1430243809

More Books

Students also viewed these Databases questions

Question

What is a memristor, and how does it store data?

Answered: 1 week ago

Question

600acog = k(27(1-cosg ))(2asin 9) where a= 300 mm 3ccmm

Answered: 1 week ago

Question

L A -r- P[N]

Answered: 1 week ago