Excel small project help

Anonymous
timer Asked: Dec 14th, 2017
account_balance_wallet $25

Question Description

I did this one and wasn't able to do it quite well, I got stuck on the COUNTIFs functions questions 10 and on. he project will need to be from step to the ending question on the word doc. You will need to complete those into the excel sheet.


I will review the sheet and then ask as to how you did the countif functions. how did you know which ones to input?

Unformatted Attachment Preview

Call Hour Reason Dept Call Hour Reason Dept 8 2 Accounting 16 1 Public_Affairs 15 2 Accounting 3 1 Public_Affairs 21 1 Admissions 15 3 Public_Affairs 22 1 Human_Resources 12 2 Admissions 19 3 Accounting 15 1 Financial_Aid 19 1 Public_Affairs 14 2 Accounting 12 4 Public_Affairs 20 2 Public_Affairs 14 2 Public_Affairs 11 2 Public_Affairs 15 1 Admissions 16 1 Admissions 10 2 Public_Affairs 15 2 Admissions 1 2 Public_Affairs 9 3 Admissions 13 2 Public_Affairs 20 3 Admissions 17 2 Admissions 11 1 Admissions 14 2 Admissions 17 3 Public_Affairs 21 1 Financial_Aid 16 1 Public_Affairs 2 1 Admissions 13 1 Accounting 11 3 Public_Affairs 18 3 Admissions 20 2 Accounting 23 2 Public_Affairs 8 1 Public_Affairs 8 2 Admissions 5 1 Human_Resources 21 1 Public_Affairs 20 2 Admissions 19 3 Admissions 4 1 Admissions 22 2 Admissions 20 1 Public_Affairs 20 3 Admissions 16 4 Financial_Aid 11 1 Public_Affairs 7 2 Public_Affairs Call Length Satisfaction Rating Call Day On Hold Call Length 6 6.7 5.5 3.7 6.5 8.8 6.3 2 5.4 5.9 3.3 4.9 4 4.3 5.3 5.1 3.5 5.4 3.6 7.2 3.3 6.3 2.6 7.2 1.8 2.5 4.7 7.6 5.7 3 3.7 1.5 3.3 4.4 5 3.2 4.5 8.1 6.5 5.7 4.5 4.8 5.9 4.4 3.2 4 4.9 5.7 5 Satisfaction Rating Call Day 4.5 4 3.7 5 5.9 1 9.2 2 5.9 1 6 3 6.4 4 7.2 2 7.6 6 4.2 5 9.2 3 5.1 3 5.9 5 8.2 2 6.7 5 6.8 5 6.5 2 8.4 6 6.8 5 4.2 7 9.9 7 8.3 6 6.5 4 6.6 5 7.5 2 8.3 1 6.6 2 5.3 3 5.4 7 6.7 5 9.4 4 7.8 1 9.3 1 5.4 5 4 1 8 3 4.9 5 4.1 4 6.2 4 8.5 6 5.6 7 9 2 8.2 4 4.5 7 9.4 7 3.8 3 6.5 2 6.2 5 9.9 5 On Hold Y Y N N N N N Y N Y N N N N N N Y N Y Y N N Y N N N N N Y Y N N N Y Y Y Y Y N N N N N Y N Y N Y N 20171213232402pack_e03ch05_grader_h1_callcenter.xlsx Issue Grade Weekday 12 16 20 14 12 6 17 21 6 20 20 22 3 9 13 23 24 10 20 9 14 11 18 20 6 6 13 21 23 10 19 13 18 18 12 19 11 4 24 21 12 14 14 9 14 12 19 4 8 7 1 11 15 10 12 9 5 20 2 1 2 4 1 2 2 2 4 2 1 2 1 1 2 1 2 3 4 3 2 1 2 2 2 2 1 1 1 1 1 3 2 2 2 2 1 4 3 2 3 2 3 3 2 3 3 1 3 2 2 1 2 1 1 2 3 4 Public_Affairs Financial_Aid Public_Affairs Admissions Admissions Admissions Admissions Human_Resources Admissions Public_Affairs Admissions Public_Affairs Public_Affairs Admissions Accounting Accounting Admissions Admissions Human_Resources Human_Resources Accounting Human_Resources Public_Affairs Admissions Accounting Admissions Accounting Accounting Admissions Admissions Public_Affairs Admissions Human_Resources Human_Resources Accounting Accounting Public_Affairs Human_Resources Human_Resources Accounting Human_Resources Human_Resources Admissions Admissions Public_Affairs Admissions Human_Resources Public_Affairs Admissions Human_Resources Human_Resources Accounting Accounting Public_Affairs Admissions Public_Affairs Admissions Accounting 20171213232402pack_e03ch05_grader_h1_callcenter.xlsx 4.6 3.5 5.7 4 3.1 3.5 2.7 5 2.7 6.1 6 4.7 5.3 3.4 4.9 5.5 4.5 7.3 4.5 5.8 4.2 3.8 4.5 3.5 1.4 3.8 4.5 4.6 4.5 2.6 6.3 1.3 4.9 4.8 3.9 6.8 6.2 5 2.4 6.8 3.1 3.2 7.8 5.4 6.6 3.5 5.7 6.3 8.3 4 4.8 3.1 5 5.5 5.2 4.6 2.2 6.1 8.8 8.3 7.1 6.1 9.6 10 8.1 5 7.7 6.7 4.4 8.1 5 8.9 6.3 7.2 7.1 3.8 6.5 4.9 7.6 10 8.1 10 9.5 10 6.1 6.8 6.6 7.3 7.7 8.8 5.5 5.4 8 6.6 8.7 6.2 8.1 6 8.3 7.4 6.9 4.8 5.8 8.6 7.2 6.5 6 7.2 7.7 9.8 4.6 8.9 5 8.9 7.3 7.8 4 1 1 2 5 7 1 7 3 6 1 3 4 6 2 3 3 6 1 2 3 5 2 2 4 2 2 3 5 4 1 4 2 6 1 4 7 2 6 4 6 1 1 2 6 5 3 5 6 6 4 6 3 5 7 1 1 6 N N N N N N N Y N N Y N Y N Y N N Y N Y N N N N N N N N Y Y N N N Y N Y N N N N N N N N Y Y N N N N N N Y N Y N Y N 24 20 9 10 14 22 15 2 10 2 18 23 1 3 3 3 2 4 3 4 1 1 3 1 Accounting Admissions Financial_Aid Accounting Accounting Accounting Financial_Aid Financial_Aid Admissions Admissions Financial_Aid Financial_Aid 20171213232402pack_e03ch05_grader_h1_callcenter.xlsx 4.4 6 3.9 5.2 4.6 6.5 7.1 6.3 4.5 7.4 5.4 5.3 8.3 4.7 10 7.2 5.5 8.3 5.1 6.6 8.5 6.4 6.9 5 4 5 7 2 3 3 1 7 4 5 5 2 N Y N N N N Y N N N N Y Department Public Affairs Human Resources Admissions Accounting Financial Aid Public Affairs 1 Frozen screen 2 Voicemail 3 Other 4 Day of Week Financial Aid Sunday Satisfaction Rating 0 4 6 8 9 Monday Grade F D C B A+ 20171213232402pack_e03ch05_grader_h1_callcenter.xlsx Tuesday Wednesday Human Resources Blackboard Electrical Internet Intranet Thursday Admissions Banner Intranet Blackboard Friday Accounting Blackboard Internet Saturday Financial Aid Internet Blackboard Intranet Internet Call Center Report Financial_Aid Department Issues # Calls on Issue # On Hold Status Department Average Call Length Public_Affairs Human_Resources Admissions Accounting Financial_Aid Total Calls Below C Notes Call Hour Dept Call Length Satisfaction Rating 1 2 3 4 Reason Satisfaction Rating Call Length Count Average Sum Max Min 20171213232402pack_e03ch05_grader_h1_callcenter.xlsx Longest Call Department Score Call Day On Hold Issue Grade Weekday 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 3 4 5 6 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 4.000 Select the range E1:I5, and create named ranges using the top row as the range values. 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. 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. 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. 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. 9.000 6.000 4.000 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 Updated: 10/17/2017 1 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Step Excel Project Points Possible Instructions On the CallCenterReport worksheet, add formulas that will summarize the issues for the department entered in cell B3. 9 10 11 12 13 14 15 16 17 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. 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. 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. 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. 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. 4.000 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 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. 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. In cell H11, add a MAX function that will show the maximum call length minutes using the Call_Length named range. 4.000 4.000 4.000 4.000 4.000 8.000 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. 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 On the CallCenterReport worksheet, in cell G19, type Y. In cell J19, type Friday. Name the range A18:J19 Call_Criteria. 18 19 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. 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. Updated: 10/17/2017 2 7.000 16.000 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Step 20 Excel Project Points Possible Instructions Save the workbook, exit Excel, and then submit your file as directed by your instructor. 0.000 Total Points Updated: 10/17/2017 3 100.000 Current_Instruction.docx ...
Purchase answer to see full attachment

Tutor Answer

uoscar
School: University of Virginia

The solution is attached.Please give me feedback if there are any issues or you need any clarification.

Call Hour

Call Hour
16
10
13
16
23
8
11
3
14

Reason

Reason
1
2
2
1
2
1
1
1
2

Dept
Public_Affairs

Call Length

Dept
Public_Affairs
Public_Affairs
Public_Affairs
Public_Affairs
Public_Affairs
Public_Affairs
Public_Affairs
Public_Affairs
Public_Affairs

Call Length
6.7
3.6
2.6
3
3.2
4.5
5.7
5.3
6.6

Satisfac...

flag Report DMCA
Review

Anonymous
awesome work thanks

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors