Description
this homework is very easy, i hope someone can help me go finish it, i can pay some tip, thank you so much.
Unformatted Attachment Preview
Purchase answer to see full attachment
Explanation & Answer
Hello! I have attached the answer in a excel document for you. :) Let me know if you have any further questions.
Office 2016 – myitlab:grader – Instructions
Step
1
2
3
4
5
6
7
8
9
10
11
Excel Project
Instructions Done
Start Excel.
Open the downloaded Excel file named e03ch05_grader_h1_CallCenter.xlsx.
Save the file with the name e03_grader_h1_CallCenter_LastFirs
Creating the following named ranges to use within formulas:
A2:A6 as Department
A14:B18 as GradeScale
B9:H9 as DayofWeek
Selecting the range E1:I5, and creating named ranges using the top row as the range values.
Selecting 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.
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.
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.
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.
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.
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 crite...