Excel Formula for average differences between different rows for the same ID

[kenderweasel]

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

[TABLE="width: 304"]
<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>[TR]
[TD="width: 127, bgcolor: transparent, align: left"]Customer ID[/TD]
[TD="width: 127, bgcolor: transparent, align: left"]Date[/TD]
[TD="width: 77, bgcolor: transparent, align: left"]Chat Start[/TD]
[TD="width: 75, bgcolor: transparent, align: left"]Chat End[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]28/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]12:04[/TD]
[TD="bgcolor: transparent, align: right"]12:16[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]432[/TD]
[TD="bgcolor: transparent, align: right"]28/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]15:08[/TD]
[TD="bgcolor: transparent, align: right"]15:12[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]46[/TD]
[TD="bgcolor: transparent, align: right"]28/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]02:19[/TD]
[TD="bgcolor: transparent, align: right"]03:21[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]28/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]07:56[/TD]
[TD="bgcolor: transparent, align: right"]08:05[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]28/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]16:54[/TD]
[TD="bgcolor: transparent, align: right"]17:05[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]28/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]13:04[/TD]
[TD="bgcolor: transparent, align: right"]13:22[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]432[/TD]
[TD="bgcolor: transparent, align: right"]28/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]11:17[/TD]
[TD="bgcolor: transparent, align: right"]12:05[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]46[/TD]
[TD="bgcolor: transparent, align: right"]28/08/2018[/TD]
[TD="bgcolor: transparent, align: right"]09:15[/TD]
[TD="bgcolor: transparent, align: right"]09:32[/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Can you use these? Note all the time values have been formatted as [h]:mm in Custom Formats;

Book1
	A	B	C	D	E
1	Customer ID	Date	Chat Start	Chat End
2	46	28-08-18	2:19	3:21
3	3	28-08-18	7:56	8:05
4	46	28-08-18	9:15	9:32
5	432	28-08-18	11:17	12:05
6	7	28-08-18	12:04	12:16
7	7	28-08-18	13:04	13:22
8	432	28-08-18	15:08	15:12
9	7	28-08-18	16:54	17:05
10
11	Customer ID	AVG
12	7	2:10
13
14	Customer ID	Chat Start	Chat End	DIFFERENCE
15	7	12:04	12:16	0:48
16	7	13:04	13:22	3:32
17	7	16:54	17:05
18
19	ALL CHATS	0:22
20
21	Date	Customer ID	Chat Start	Chat End	DIFFERENCE
22	28-08-18	46	2:19	3:21	1:02
23		3	7:56	8:05	0:09
24		46	9:15	9:32	0:17
25		432	11:17	12:05	0:48
26		7	12:04	12:16	0:12
27		7	13:04	13:22	0:18
28		432	15:08	15:12	0:04
29		7	16:54	17:05	0:11
Sheet1

Cell Formulas
Range	Formula
B12	=AVERAGEIF($D$15:$D$17,"<>0")
B19	=AVERAGEIF($E$22:$E$29,"<>0")
B15	{=IFERROR(INDEX(C$2:C$9,SMALL(IF($A$2:$A$9=$A$12,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(A$15:A15))),"")}
B22	{=IFERROR(INDEX($A$2:$A$9,SMALL(IF($B$2:$B$9=$A$22,ROW($B$2:$B$9)-ROW($B$2)+1),ROWS($B$22:B22))),"")}
D15	=IFERROR(B16-C15,"")
D22	{=IFERROR(INDEX($D$2:$D$9,SMALL(IF($B$2:$B$9=$A$22,ROW($B$2:$B$9)-ROW($B$2)+1),ROWS($B$22:B22))),"")}
E22	=IFERROR(D22-C22,"")
A15	{=IFERROR(INDEX($A$2:$A$9,SMALL(IF($A$2:$A$9=$A$12,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(A$15:A15))),"")}
C15	{=IFERROR(INDEX(D$2:D$9,SMALL(IF($A$2:$A$9=$A$12,ROW($A$2:$A$9)-ROW($A$2)+1),ROWS(B$15:B15))),"")}
C22	{=IFERROR(INDEX($C$2:$C$9,SMALL(IF($B$2:$B$9=$A$22,ROW($B$2:$B$9)-ROW($B$2)+1),ROWS($B$22:B22))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.