Can anybody answer steps 9 to 19? Please!
Y016_AL-CH05-GRADER-PS1_AW-University Call Center 1.3 Step On the depertment entered in cel 93. worksheet, add formulas that will summarize the issues for the Project Description: The University's call center needs a workbook develaped that wW use data to analyze the performance of the cal center. The cal center contacted your professor and she recommended you for the task. The workbook wW set up a variety of tools that il help assess the efficiency of the center and its staf. Your wa develop a workbook for the cal center manager that www hep with the center's data analysis. 9n cell B6, add an INDEX function that wil use an INDIRECT function to retrieve the department issue list for the department Isted in cell 83. Use an absolute reference to 83, and then use a relative cell reference to A6 as the row num argument. Copy the formua down to cell 69 In cell C6, add a CoUNTIFS function that will count the number of departments on the CalData worksheet. Use the Dept named range as criteria range1, and then use an absolute reference to 83 as criteria1. Use the Reason named range as criteria renge, and then use cell AG as criteria2. Copy the formula down to cel C9. 10 For the purpose of grading the project you are required to perform the following tasks: tht wil count the number Step depertment listed in 93. Use the Dept named range as criteria rangei, and then use an 11 / | absolute reference to B3 as aterial. Use the Reason named range as criteria-range2, and Start Excel. Open the downicaded Excel fie named edtchoS grader hi CalContersx Save | the fie with the name e03-grader,h1-CallCenter-LastFirst, using your ast and first hon use cel AS as critoria2. Use the On Hold named range as criteria range3, and thon usc Y as criterla3, Copy the formula down to cell D3 In cell E6, add an IF statement with a nested AND that will orter status notice. tf the number of cas on ssue in cel Cs is greater than 3 and the number of cals on hold in cell D6 s greater than 2, then "Check Hold Issue" shouid display. Otherwise, nothing shoud display, Copy the formua dovwn to cell E9. 1' ronges to usc 12 A2:A6 as Department A14: B 18 as GradeScale B9:H9 as DayofWeek average named range and the criteria specified in cell A12. Select the r Select the Cal Duta worksheet. In cell A9. the current data set. Apply White, Table Style Light 1. In cell H9, type Issue as the ficd label In cell I9, type Grade as the ficld label. In cel 19, type Weekday as the field label. E1:IS, and create named the top row as the values 13 In cell C12, add a CouNTIF function that will ccunt the number of calls for the Dept named range and the critena specified in cel A12. the deta as an Excel tebie with headers using n cell D12, add a formula that sums two COUNTIFS formulas. The first COUNTIFS wil count the number of cals asxociated with the Dept named range and criteria specified in cel A12 that received ogradc F, and the second COUNTIPS will do the same for grade D. 14 Note, depending upon the Office version used, the table style name may be Tabie Style Light add an IF staternent using a eny notes elect range then name the entire data associated with the issue f there are more than 10 bsu reported with a grade belaw a Cin cell 012, or when the number of scores less than C divided by the total cals in cell C12 is including the labels, as CallDataAll Next, create named ranges using the top row as the 15 value than SO%, ten "Explore Issues" should display. Otherwise, the result wil be blank. y the formulas in B12 E12 down through row B16 EIS named range listed in column C (C10) and use the Rcason ficls in column 8 (B10) as the row Call-Length named range. 5 number to return for the department name in the refcrenced named range. 16 hest the function inside an [FArction so that issues currently displaying asa C will display as a blank celL Resize the coumn width as needed. In cell I10, enter a VLOOKUP function that will convert the Satstaction Rating to a grade found in the second coumn of Uhe GradeScale named range. The formula is not locking for on exact match. In cell H12, add an INDEX function that wil use the Dept named range as the associated with a MATCH function to determine the lockup array for the longest referenced in H11 from the Can Length named rarge wth an exact match, In cell H13, add an INDEX function that will pull the satisfaction rating associated vaith a MATCH furction to determine the lookup array for the longest call referenced in H11 from the Call Length range with an exact match 17 In cell 110, enter an INDEX function that will corvert the Cal Day to the actual weekday found in row 1 of the DayofWeek named range. Resive the column wicth as needed. range A18:319 Call Criteria. n cell 822, add a CCOUNT functicn for the CaDetall database to find the count of the On the CallData worksheet: In cel C2, type Public Affairs in cel G2, type Y Run an advanced filter on the table data set isted in cel B21 the Call Criteria named n 923:926, add dotabase functions that find the DAVERAGE (23), DSUM (824), DMAX (825), and DMIN (826) for the CallData All named range. Sclet the cell range 822:926, and then copy the formulas to column C the crteia r A1:12 19 Usc named range i in the formulas. Y016_AL-CH05-GRADER-PS1_AW-University Call Center 1.3 Step On the depertment entered in cel 93. worksheet, add formulas that will summarize the issues for the Project Description: The University's call center needs a workbook develaped that wW use data to analyze the performance of the cal center. The cal center contacted your professor and she recommended you for the task. The workbook wW set up a variety of tools that il help assess the efficiency of the center and its staf. Your wa develop a workbook for the cal center manager that www hep with the center's data analysis. 9n cell B6, add an INDEX function that wil use an INDIRECT function to retrieve the department issue list for the department Isted in cell 83. Use an absolute reference to 83, and then use a relative cell reference to A6 as the row num argument. Copy the formua down to cell 69 In cell C6, add a CoUNTIFS function that will count the number of departments on the CalData worksheet. Use the Dept named range as criteria range1, and then use an absolute reference to 83 as criteria1. Use the Reason named range as criteria renge, and then use cell AG as criteria2. Copy the formula down to cel C9. 10 For the purpose of grading the project you are required to perform the following tasks: tht wil count the number Step depertment listed in 93. Use the Dept named range as criteria rangei, and then use an 11 / | absolute reference to B3 as aterial. Use the Reason named range as criteria-range2, and Start Excel. Open the downicaded Excel fie named edtchoS grader hi CalContersx Save | the fie with the name e03-grader,h1-CallCenter-LastFirst, using your ast and first hon use cel AS as critoria2. Use the On Hold named range as criteria range3, and thon usc Y as criterla3, Copy the formula down to cell D3 In cell E6, add an IF statement with a nested AND that will orter status notice. tf the number of cas on ssue in cel Cs is greater than 3 and the number of cals on hold in cell D6 s greater than 2, then "Check Hold Issue" shouid display. Otherwise, nothing shoud display, Copy the formua dovwn to cell E9. 1' ronges to usc 12 A2:A6 as Department A14: B 18 as GradeScale B9:H9 as DayofWeek average named range and the criteria specified in cell A12. Select the r Select the Cal Duta worksheet. In cell A9. the current data set. Apply White, Table Style Light 1. In cell H9, type Issue as the ficd label In cell I9, type Grade as the ficld label. In cel 19, type Weekday as the field label. E1:IS, and create named the top row as the values 13 In cell C12, add a CouNTIF function that will ccunt the number of calls for the Dept named range and the critena specified in cel A12. the deta as an Excel tebie with headers using n cell D12, add a formula that sums two COUNTIFS formulas. The first COUNTIFS wil count the number of cals asxociated with the Dept named range and criteria specified in cel A12 that received ogradc F, and the second COUNTIPS will do the same for grade D. 14 Note, depending upon the Office version used, the table style name may be Tabie Style Light add an IF staternent using a eny notes elect range then name the entire data associated with the issue f there are more than 10 bsu reported with a grade belaw a Cin cell 012, or when the number of scores less than C divided by the total cals in cell C12 is including the labels, as CallDataAll Next, create named ranges using the top row as the 15 value than SO%, ten "Explore Issues" should display. Otherwise, the result wil be blank. y the formulas in B12 E12 down through row B16 EIS named range listed in column C (C10) and use the Rcason ficls in column 8 (B10) as the row Call-Length named range. 5 number to return for the department name in the refcrenced named range. 16 hest the function inside an [FArction so that issues currently displaying asa C will display as a blank celL Resize the coumn width as needed. In cell I10, enter a VLOOKUP function that will convert the Satstaction Rating to a grade found in the second coumn of Uhe GradeScale named range. The formula is not locking for on exact match. In cell H12, add an INDEX function that wil use the Dept named range as the associated with a MATCH function to determine the lockup array for the longest referenced in H11 from the Can Length named rarge wth an exact match, In cell H13, add an INDEX function that will pull the satisfaction rating associated vaith a MATCH furction to determine the lookup array for the longest call referenced in H11 from the Call Length range with an exact match 17 In cell 110, enter an INDEX function that will corvert the Cal Day to the actual weekday found in row 1 of the DayofWeek named range. Resive the column wicth as needed. range A18:319 Call Criteria. n cell 822, add a CCOUNT functicn for the CaDetall database to find the count of the On the CallData worksheet: In cel C2, type Public Affairs in cel G2, type Y Run an advanced filter on the table data set isted in cel B21 the Call Criteria named n 923:926, add dotabase functions that find the DAVERAGE (23), DSUM (824), DMAX (825), and DMIN (826) for the CallData All named range. Sclet the cell range 822:926, and then copy the formulas to column C the crteia r A1:12 19 Usc named range i in the formulas
