CODEWITHSUNDEEP
excel
LeeKing
LeeKing

Reputation: 71

excel:how to calculate average interval time

+-------------+----------+----------+--------+------------------+
| customer_id |   date   |   time   | answer | missed_call_type |
+-------------+----------+----------+--------+------------------+
|         101 | 2018/8/3 | 12:13:00 | no     | employee         |
|         102 | 2018/8/3 | 12:15:00 | no     | customer         |
|         103 | 2018/8/3 | 12:20:00 | no     | employee         |
|         102 | 2018/8/3 | 15:15:00 | no     | customer         |
|         101 | 2018/8/3 | 18:15:00 | no     | employee         |
|         105 | 2018/8/3 | 18:18:00 | no     | customer         |
|         102 | 2018/8/3 | 19:18:00 | no     | employee         |
+-------------+----------+----------+--------+------------------+

I got a table whick looks like this and wanted to calculate average interval time for those who did not answer the phone. for this example,the average interval time is:

{(18:15:00-12:13:00)+[(19:18:00-15:15:00)+(15:15:00-12:15:00)]/2}/2

the problem is I could only manipulate it in Excel...someone knows Excel please help!or any suggestion is fine,I am familiar with SQL.

Upvotes: 1

Views: 1120

Answers (1)

Prebsus
Prebsus

Reputation: 695

I've entered your data as shown below, and added a time_to_next_call-column which calculates the interval until the next call to the same customer_id using the formula: =INDEX(C3:C$8,MATCH(A2,A3:A$8,0))-C2 for the first cell in the data set.

enter image description here

Then, list your customer IDs in a column, and use and AVERAGEIF-function to calcualte the average time_to_next_call for that customer_ID, as shown below:

enter image description here

As I've placed the customer_id 101 in cell A12, my function for calculating the average time_to_next_call is: =AVERAGEIFS($F$2:$F$8,$A$2:$A$8,A13,$F$2:$F$8,">0")

Adjust your ranges as appropriate to fit your workbook. Hope this helps

Upvotes: 1

Related Questions