Nested If and Functions

[nickwh34]

Hi All

I have a spreadsheet with 2000 rows of information, What i am trying to do check the Numbers column if the cell values match and set the first time of call to 0 i:e the 0 to represent the initial call and then work out the time difference between additional calls in the time bucket field. I:e The difference between inital call and the actual next call time. I can get the formulas to work over 2 cells but if there are 3 or more calls from a number the formulas fall over. Does any one have a formula that can check if the cells either side of a number match then set the first date and time it appears to 0 and every other time after that display the difference.

Numbers</SPAN>	Route Id</SPAN>	CallStartTime</SPAN>	Time Bucket</SPAN>
14357</SPAN>	33451867</SPAN>	14/11/2013 16:11:05</SPAN>	0:00:47</SPAN>
14357</SPAN>	33444609</SPAN>	14/11/2013 16:10:18</SPAN>	0:01:52</SPAN>
14357</SPAN>	33444405</SPAN>	14/11/2013 16:08:26</SPAN>	0:00:00</SPAN>
64005</SPAN>	33685217</SPAN>	17/11/2013 11:36:48</SPAN>	11:18:32</SPAN>
64005</SPAN>	33682097</SPAN>	17/11/2013 00:18:16</SPAN>	0:00:00</SPAN>
107540</SPAN>	33588041</SPAN>	15/11/2013 16:27:47</SPAN>	28:46:22</SPAN>
107540</SPAN>	33382721</SPAN>	14/11/2013 11:41:25</SPAN>	22:55:30</SPAN>
107540</SPAN>	33244793</SPAN>	13/11/2013 12:45:55</SPAN>	0:00:00</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>

 

[Snakehips]

nickwh34,

Welcome to MrExcel.

Try this...

Excel 2007

	A	B	C	D
1	Numbers	Route Id	CallStartTime	Time Bucket
2	14357	33451867	16:11:05	0:00:47
3	14357	33444609	16:10:18	0:01:52
4	14357	33444405	16:08:26	0:00:00
5	64005	33685217	11:36:48	11:18:32
6	64005	33682097	00:18:16	0:00:00
7	107540	33588041	16:27:47	28:46:22
8	107540	33382721	11:41:25	22:55:30
9	107540	33244793	12:45:55	0:00:00

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula D2 =IF(A3=A2,C2-C3,0) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Time bucket cells formatted [h]:mm:ss;@

Hope that helps.

 

[Snakehips]

nickwh34,

Initially, I took it that the data shot you posted was your desired result.

Having re-read your post I'm thinking that you don't want the time difference between successive calls but the difference of each call time compared to the initial call.

That being so, and provided that the Numbers are sorted into unique sets, as posted, then perhaps this is what you need.....

Excel 2007
	A	B	C	D
1	Numbers	Route Id	CallStartTime	Time Bucket
2	14357	33451867	14/11/13 16:11:05	0:02:39
3	14357	33444609	14/11/13 16:10:18	0:01:52
4	14357	33444405	14/11/13 16:08:26	0:00:00
5	64005	33685217	17/11/13 11:36:48	11:18:32
6	64005	33682097	17/11/13 00:18:16	0:00:00
7	107540	33588041	15/11/13 16:27:47	74:41:52
8	107540	33382721	14/11/13 11:41:25	45:55:30
9	107540	33244793	13/11/13 12:45:55	23:00:00
10	107540	33244793	12/11/13 13:45:55	0:00:00
Sheet1 (2)

Cell Formulas
Range	Formula
D2	=IFERROR(IF(A3=A2,C2-INDEX(C:C,(COUNTIF(A:A,A2)-1)+(MATCH(A2,A:A,0)),0),0),"")

 

[nickwh34]

[
Steve thanks so much this formula worked a treat spot on and exactly what I wanted can't thank you enough

Nick



QUOTE=Snakehips;3648220]nickwh34,

Initially, I took it that the data shot you posted was your desired result.

Having re-read your post I'm thinking that you don't want the time difference between successive calls but the difference of each call time compared to the initial call.

That being so, and provided that the Numbers are sorted into unique sets, as posted, then perhaps this is what you need.....

Excel 2007
	A	B	C	D
1	Numbers	Route Id	CallStartTime	Time Bucket
2	14357	33451867	14/11/13 16:11:05	0:02:39
3	14357	33444609	14/11/13 16:10:18	0:01:52
4	14357	33444405	14/11/13 16:08:26	0:00:00
5	64005	33685217	17/11/13 11:36:48	11:18:32
6	64005	33682097	17/11/13 00:18:16	0:00:00
7	107540	33588041	15/11/13 16:27:47	74:41:52
8	107540	33382721	14/11/13 11:41:25	45:55:30
9	107540	33244793	13/11/13 12:45:55	23:00:00
10	107540	33244793	12/11/13 13:45:55	0:00:00
Sheet1 (2)

Cell Formulas
Range	Formula
D2	=IFERROR(IF(A3=A2,C2-INDEX(C:C,(COUNTIF(A:A,A2)-1)+(MATCH(A2,A:A,0)),0),0),"")

[/QUOTE]

 

[Snakehips]

Cheers Nick.

Who the devil is Steve????