Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
Homework answers / question archive / In the range L4:N13, Tiana set up an area to summarize some data about the service representatives handling the customer support calls
In the range L4:N13, Tiana set up an area to summarize some data about the service representatives handling the customer support calls. First, she wants to count the number of calls each service representative handled. In cell M5, enter a formula using the COUNTIF function and a structured reference to the Service Rep column in the Calls table to count the number of calls Barry (cell L5) handled. Fill the formula into the range M6:M13 without formatting.
Tiana also wants to average the combined scores for each employee. In cell N5, enter a formula using the AVERAGEIF function and structured references to the Service Rep and Average columns in the Calls table to average the scores Barry (cell L5) received for all the calls he handled. Fill the formula into the range N6:N13 without formatting.
Pierce Software 2
Customer Call Log: January-March
Rep Barry Bruno Calder Darego Shanahan Thanh Volmann Wagner Zimmerman Total Calls Average Score 0 #DIV/0! 4.00 3.63 4.75 2.63 2.38 0 #DIV/0! 1 2.88 0 #DIV/0! 3 Customer Survey Scores Question Question Question Question 4 Call ID - Date Service Rep - Call Code Call Type - 1 - 2 - Average 5 20191 1/4/2021 Wagner 7 Call Type 4 5 2 4 3.75 6 20154 1/4/2021 Thanh 1 Feature request 2 1 3 1 1.75 7 21170 1/4/2021 Darego 17 Bug report 5 5 5 5 5.00 8 21145 1/5/2021 Calder 16 Tech problem 4 5 5 3 5 4.25 9 22103 1/5/2021 Shanahan 21 Tech problem 4 4 4 4 4 4.00 10 22246 1/6/2021 Bruno 12 Cancellation 4. 5 3 4 4.00 11 22137 1/7/2021 Thanh 7 How to 3 3 3 3 3.00 12 22234 1/7/2021 Shanahan 1 Feature request 2 1 1 1 1 1.25 13 23228 1/7/2021 Wagner 2. Bug report 3 2 2 1 2.00 14 23215 1/8/2021 Calder 8 Bug report 4 3 3 2 3.00 15 24117 1/9/2021 Darego 25 Feature request 5 5 3 3 5 4.50 16 24140 1/9/2021 Bruno 9 Cancellation 3 3 3 3 3 2 2.75 17 25224 1/11/2021 Thanh 3 Feature request 4 5 4 4 4.25 18 23126 1/12/2021 Volmann 2 Bug report 5 5 4 4.75 19 25210 1/12/2021 Barry 12 Bug report 3 2 2 2 1 2.00 20 20189 1/14/2021 Wagner How to 1 1 1 1 1.00 21 20126 1/14/2021 Shanahan 20 Cancellation 3 4 4 2 3.25 22 26157 1/15/2021 Bruno 22 Tech problem 2 3 z 2 2.25 23 25201 1/16/2021 Thanh 16 Cancellation 4 3 3 4 3.50 24 26149 1/16/2021 Calder 19 Tech problem 5 4 4 5 5 4.75 Graded Summary Report Documentalion Customer Call Log Call Type by Rep Scores by Dale Survey Questio ...
Answer:
1. Total Calls:
Select cell M5, and put formula =COUNTIF($C$5:$C$24,"="&L5)
The result will count all the records in C5:C24 and include the records where the Service Rep name is = L5.
Drag the value in M5 till M13, which will fill the other values also:
2. Average of Service Rep:
Select cell N5, and put formula =AVERAGEIF($C$5:$C$24, "="&L5,$J$5:$J$24)
Where:
AVERAGEIF : name of the function
$C$5:$C$24: Range to be checked for average calculation
“=”&L5: condition which should match with value in C
$J$5:$J$24: Range to calculate average
The result will average of all the records in J5:J24 and include the records where the Service Rep name is = L5.
Drag till N13, where few records will have error #DIV/0! Because the total number of records for that sales rep is 0 and average is dividing the result with 0.
To overcome this problem, use IFERROR function, which will return 0 when a call count is 0 as below:
=IFERROR(AVERAGEIF(C5:C24, "="&L5,J5:J24),0)
PFA
