kenderweasel
New Member
- Joined
- Feb 17, 2017
- Messages
- 40
Hi,
I have a spreadsheet which lists a customer ID, Chat Start Time, Chat End Time, and Date. I need a formula that will look up the chat end time of the first interaction with a particular customer ID, then work out the time difference between that and the chat start time of the next interaction with the same customer, and so on - resulting in a single figure for that customer that gives the average difference between the individual chats. I then need to return an average time between chats for all customers with a particular number of interactions on a particular date. I think I need an array formula, but I don't know where to start
<colgroup><col width="127" style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;" span="2">
<col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2816;">
<col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;">
<tbody>
</tbody>
I have a spreadsheet which lists a customer ID, Chat Start Time, Chat End Time, and Date. I need a formula that will look up the chat end time of the first interaction with a particular customer ID, then work out the time difference between that and the chat start time of the next interaction with the same customer, and so on - resulting in a single figure for that customer that gives the average difference between the individual chats. I then need to return an average time between chats for all customers with a particular number of interactions on a particular date. I think I need an array formula, but I don't know where to start
Customer ID | Date | Chat Start | Chat End |
7 | 28/08/2018 | 12:04 | 12:16 |
432 | 28/08/2018 | 15:08 | 15:12 |
46 | 28/08/2018 | 02:19 | 03:21 |
3 | 28/08/2018 | 07:56 | 08:05 |
7 | 28/08/2018 | 16:54 | 17:05 |
7 | 28/08/2018 | 13:04 | 13:22 |
432 | 28/08/2018 | 11:17 | 12:05 |
46 | 28/08/2018 | 09:15 | 09:32 |