Expert answer:Excel small project help
Expert answer: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?
pack_e03ch05_grader_h1_callcenter.xlsx
yo16xlch05graderps1hw___university_call_center_13_instructions__1_.docx
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
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades