Find all dates related to a client and then find the average time difference between them

[corruptedlogic]

Hi there, I have an export from our zendesk helpdesk and am trying to find the average time to solve a ticket. So far, I have tried to sum the Open date and the solved dates, then average the result. that seems to get me the correct answer ( i think!) until the first "solved date" is blank (because the first row for a client is something other than a solved or closed ticket), in that scenario i just get a whole bunch of ###### characters. I'm hoping someone can point me in the right direction to either only iterate through solved or closed tickets or somehow ignore the blanks in the solved date column. Totally open to suggestions!

The formulae i currently have are:

Sum solved dates: =INDEX(E:E,MATCH(M2,C:C,0))
Sum Open Dates: =INDEX(D:D,MATCH(M2,C:C,0))
Calc average solve time: =(I4-I3)*24

Thanks to all in advance, sample data set attached.

	A	B	C	D	E	F	G	H	I	J	K	L	M
id	Status	Organization	Request date	Solved date	Latest update
Open	Client 1	Average hours to solve	Client	Client 1
Open	Client 1	sum Open
Closed	Client 1	sum Solve
On-hold	Client 1
Solved	Client 1
Closed	Client 1
Solved	Client 1
Open	Client 1
Pending	Client 1
Closed	Client 1
On-hold	Client 1
Closed	Client 1
Pending	Client 1
On-hold	Client 1
Closed	Client 1
Closed	Client 1
Closed	Client 1
Closed	Client 1
Pending	Client 1
Closed	Client 1
Closed	Client 1
Closed	Client 1
Closed	Client 2
Solved	Client 2
Closed	Client 2
Closed	Client 2
Closed	Client 2
Closed	Client 2
Closed	Client 2
Closed	Client 2
Closed	Client 2
Closed	Client 2
Closed	Client 2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2100[/TD]

[TD="align: right"]3/8/2018 11:42[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/8/2018 11:50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]########[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2098[/TD]

[TD="align: right"]3/8/2018 9:08[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/8/2018 9:12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]43167.49[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2082[/TD]

[TD="align: right"]3/6/2018 10:29[/TD]
[TD="align: right"]3/6/2018 10:31[/TD]
[TD="align: right"]3/6/2018 10:31[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2076[/TD]

[TD="align: right"]3/5/2018 13:58[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/6/2018 8:38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2072[/TD]

[TD="align: right"]3/5/2018 11:18[/TD]
[TD="align: right"]3/5/2018 11:35[/TD]
[TD="align: right"]3/5/2018 11:35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2069[/TD]

[TD="align: right"]3/2/2018 10:35[/TD]
[TD="align: right"]3/6/2018 8:38[/TD]
[TD="align: right"]3/6/2018 8:38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2068[/TD]

[TD="align: right"]3/2/2018 10:14[/TD]
[TD="align: right"]3/8/2018 9:19[/TD]
[TD="align: right"]3/8/2018 9:19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2060[/TD]

[TD="align: right"]3/1/2018 10:03[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/1/2018 10:53[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2053[/TD]

[TD="align: right"]2/28/2018 10:13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/6/2018 7:02[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2050[/TD]

[TD="align: right"]2/27/2018 12:06[/TD]
[TD="align: right"]2/27/2018 12:39[/TD]
[TD="align: right"]3/3/2018 13:06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2044[/TD]

[TD="align: right"]2/26/2018 17:33[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2/27/2018 14:25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2043[/TD]

[TD="align: right"]2/26/2018 17:20[/TD]
[TD="align: right"]3/6/2018 8:38[/TD]
[TD="align: right"]3/6/2018 8:38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2041[/TD]

[TD="align: right"]2/26/2018 13:58[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/5/2018 10:28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2038[/TD]

[TD="align: right"]2/26/2018 10:27[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/8/2018 12:08[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2037[/TD]

[TD="align: right"]2/23/2018 14:44[/TD]
[TD="align: right"]2/27/2018 12:53[/TD]
[TD="align: right"]3/3/2018 13:06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]2021[/TD]

[TD="align: right"]2/22/2018 7:29[/TD]
[TD="align: right"]2/23/2018 8:35[/TD]
[TD="align: right"]2/27/2018 9:05[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]2019[/TD]

[TD="align: right"]2/20/2018 17:08[/TD]
[TD="align: right"]2/27/2018 12:53[/TD]
[TD="align: right"]3/3/2018 13:06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]2018[/TD]

[TD="align: right"]2/20/2018 16:45[/TD]
[TD="align: right"]2/27/2018 10:51[/TD]
[TD="align: right"]3/3/2018 11:07[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]2007[/TD]

[TD="align: right"]2/16/2018 13:06[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/2/2018 13:29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]1984[/TD]

[TD="align: right"]2/15/2018 10:06[/TD]
[TD="align: right"]2/15/2018 10:09[/TD]
[TD="align: right"]2/15/2018 10:09[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]1983[/TD]

[TD="align: right"]2/15/2018 9:51[/TD]
[TD="align: right"]2/15/2018 11:13[/TD]
[TD="align: right"]2/19/2018 12:10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]1982[/TD]

[TD="align: right"]2/15/2018 9:20[/TD]
[TD="align: right"]2/15/2018 10:20[/TD]
[TD="align: right"]2/19/2018 11:04[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]1634[/TD]

[TD="align: right"]12/21/2017 13:37[/TD]
[TD="align: right"]12/21/2017 14:46[/TD]
[TD="align: right"]12/25/2017 15:06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]2091[/TD]

[TD="align: right"]3/7/2018 8:36[/TD]
[TD="align: right"]3/7/2018 8:56[/TD]
[TD="align: right"]3/7/2018 8:56[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]2009[/TD]

[TD="align: right"]2/20/2018 5:33[/TD]
[TD="align: right"]2/21/2018 5:56[/TD]
[TD="align: right"]2/25/2018 6:03[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]1995[/TD]

[TD="align: right"]2/16/2018 6:19[/TD]
[TD="align: right"]2/16/2018 9:07[/TD]
[TD="align: right"]2/20/2018 10:02[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]1951[/TD]

[TD="align: right"]2/13/2018 9:51[/TD]
[TD="align: right"]2/13/2018 10:10[/TD]
[TD="align: right"]2/17/2018 11:07[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]1946[/TD]

[TD="align: right"]2/12/2018 13:29[/TD]
[TD="align: right"]2/12/2018 14:33[/TD]
[TD="align: right"]2/16/2018 15:08[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]1869[/TD]

[TD="align: right"]1/30/2018 12:06[/TD]
[TD="align: right"]2/16/2018 14:23[/TD]
[TD="align: right"]2/20/2018 15:10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]1855[/TD]

[TD="align: right"]1/29/2018 9:41[/TD]
[TD="align: right"]1/30/2018 12:21[/TD]
[TD="align: right"]2/3/2018 13:05[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]1845[/TD]

[TD="align: right"]1/25/2018 16:35[/TD]
[TD="align: right"]2/7/2018 13:11[/TD]
[TD="align: right"]2/11/2018 14:04[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]1835[/TD]

[TD="align: right"]1/24/2018 12:27[/TD]
[TD="align: right"]1/25/2018 8:30[/TD]
[TD="align: right"]1/29/2018 9:06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]1693[/TD]

[TD="align: right"]1/2/2018 8:48[/TD]
[TD="align: right"]1/2/2018 9:04[/TD]
[TD="align: right"]1/6/2018 10:07[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Zendesk Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=(I4-I3)*24[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]=INDEX(D:D,MATCH(M2,C:C,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=INDEX(E:E,MATCH(M2,C:C,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Your example only looks up the first client/date. Also, instead of averaging the sums you should have a column of differences and conditionally average them:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	id	Status	Organization	Request date	Solved date	Diff	Latest update
2	2100	Open	Client 1	3/8/2018 11:42			3/8/2018 11:50		Average hours to solve	66.83205128			Client	Client 1
3	2098	Open	Client 1	3/8/2018 9:08			3/8/2018 9:12		sum Open
4	2082	Closed	Client 1	3/6/2018 10:29	3/6/2018 10:31	0.033333333	3/6/2018 10:31		sum Solve
5	2076	On-hold	Client 1	3/5/2018 13:58			3/6/2018 8:38
6	2072	Solved	Client 1	3/5/2018 11:18	3/5/2018 11:35	0.283333333	3/5/2018 11:35
7	2069	Closed	Client 1	3/2/2018 10:35	3/6/2018 8:38	94.05	3/6/2018 8:38
8	2068	Solved	Client 1	3/2/2018 10:14	3/8/2018 9:19	143.0833333	3/8/2018 9:19
9	2060	Open	Client 1	3/1/2018 10:03			3/1/2018 10:53
10	2053	Pending	Client 1	2/28/2018 10:13			3/6/2018 7:02
11	2050	Closed	Client 1	2/27/2018 12:06	2/27/2018 12:39	0.55	3/3/2018 13:06
12	2044	On-hold	Client 1	2/26/2018 17:33			2/27/2018 14:25
13	2043	Closed	Client 1	2/26/2018 17:20	3/6/2018 8:38	183.3	3/6/2018 8:38
14	2041	Pending	Client 1	2/26/2018 13:58			3/5/2018 10:28
15	2038	On-hold	Client 1	2/26/2018 10:27			3/8/2018 12:08
16	2037	Closed	Client 1	2/23/2018 14:44	2/27/2018 12:53	94.15	3/3/2018 13:06
17	2021	Closed	Client 1	2/22/2018 7:29	2/23/2018 8:35	25.1	2/27/2018 9:05
18	2019	Closed	Client 1	2/20/2018 17:08	2/27/2018 12:53	163.75	3/3/2018 13:06
19	2018	Closed	Client 1	2/20/2018 16:45	2/27/2018 10:51	162.1	3/3/2018 11:07
20	2007	Pending	Client 1	2/16/2018 13:06			3/2/2018 13:29
21	1984	Closed	Client 1	2/15/2018 10:06	2/15/2018 10:09	0.05	2/15/2018 10:09
22	1983	Closed	Client 1	2/15/2018 9:51	2/15/2018 11:13	1.366666667	2/19/2018 12:10
23	1982	Closed	Client 1	2/15/2018 9:20	2/15/2018 10:20	1	2/19/2018 11:04
24	1634	Closed	Client 2	12/21/2017 13:37	12/21/2017 14:46	1.15	12/25/2017 15:06
25	2091	Solved	Client 2	3/7/2018 8:36	3/7/2018 8:56	0.333333333	3/7/2018 8:56
26	2009	Closed	Client 2	2/20/2018 5:33	2/21/2018 5:56	24.38333333	2/25/2018 6:03
27	1995	Closed	Client 2	2/16/2018 6:19	2/16/2018 9:07	2.8	2/20/2018 10:02
28	1951	Closed	Client 2	2/13/2018 9:51	2/13/2018 10:10	0.316666667	2/17/2018 11:07
29	1946	Closed	Client 2	2/12/2018 13:29	2/12/2018 14:33	1.066666667	2/16/2018 15:08
30	1869	Closed	Client 2	1/30/2018 12:06	2/16/2018 14:23	410.2833333	2/20/2018 15:10
31	1855	Closed	Client 2	1/29/2018 9:41	1/30/2018 12:21	26.66666667	2/3/2018 13:05
32	1845	Closed	Client 2	1/25/2018 16:35	2/7/2018 13:11	308.6	2/11/2018 14:04
33	1835	Closed	Client 2	1/24/2018 12:27	1/25/2018 8:30	20.05	1/29/2018 9:06
34	1693	Closed	Client 2	1/2/2018 8:48	1/2/2018 9:04	0.266666667	1/6/2018 10:07
Sheet6

Cell Formulas
Range	Formula
F2	=IF(E2=0,"",24*(E2-D2))
J2	=AVERAGEIF($C$2:$C$34,N2,$F$2:$F$34)

 

[corruptedlogic]

Thanks, am i understanding correctly that the result in J2 is being displayed in hours?

 

[sheetspread]

yes

 

[corruptedlogic]

Perfect, thanks so much!