Formula Based on Row Value Below - Row Value Above w/ Constant Column

[rbeccaas]

Hi,

Looking for a formula to show time lapse based on a cell above and below where the data is entered. The date/time is always in column A, but the row is ever-changing (time).

Example:

Data in B4 & B12. B5:B11 is where the value of time lapse from corresponding time in column A4 & A12 (currently typing in =a12-a4). B6:B11 is merged due to columns C thru F having data recorded at times in between the spans from A5:A11

[pre]

6/8/2013 9:19	0:05:06	0:07:15	data
6/8/2013 9:20			0:14:19	data
6/8/2013 9:22					data
6/8/2013 9:24	data
6/8/2013 9:26	0:34:18	data
6/8/2013 9:33		0:34:40	data
6/8/2013 9:34			0:15:26	data
6/8/2013 9:36					data
6/8/2013 9:48			data
6/8/2013 9:50			0:17:25	data
6/8/2013 9:52					data
6/8/2013 9:58	data
6/8/2013 10:00		data
6/8/2013 10:06			data
6/8/2013 10:07				data

[/pre]
Is there a formula that can help me manage this kind of study?


Thanks in advance!

Regards,

Rebecca

 

[NARAYANK991]

Hi Rebecca ,


I am not able to understand your requirement ; can you explain ?


I assume your date + time data in column A is as follows , starting from row 1 i.e. from A1 :

[pre]

6/8/2013 9:19
6/8/2013 9:20
6/8/2013 9:22
6/8/2013 9:24
6/8/2013 9:26
6/8/2013 9:33
6/8/2013 9:34
6/8/2013 9:36
6/8/2013 9:48
6/8/2013 9:50
6/8/2013 9:52
6/8/2013 9:58
6/8/2013 10:00
6/8/2013 10:06
6/8/2013 10:07

[/pre]
What are the other times signifying , and what does the text data signify ?


If you can have the seconds also in column A , it may be easier to correlate the times in the other columns with the times in column A.


Narayan

 

[rbeccaas]

Yes, date & time are both in column A dumped from another program, so it does also include seconds.


The other times is the duration between the data collection times represented as "data". The actual data is irrelevant, as it is to monitor a frequency of the collection.


So, B5:B11, 0:34:18 is the result of A12-A4. Column C show the elapsed time between collection of 0:34:40 between 09:26 & 10:00 (A13-A5).


Hope that clears it up. Thank you for the help!

 

[NARAYANK991]

Hi Rebecca ,


Sorry , but things are still not clear ; if you have data in columns B through F , how will you put in the formulae in the same columns ? Are you going to insert the formulae one by one in the respective cells ?


If I assume that you will have the formulae in columns G , H and beyond , then , suppose your data starts from row 2 , put in the following formula in G2 , and copy across and down :


=IF(B1="data",INDEX($A3:$A$16,MATCH("data",B3:B$16,0))-$A1,"")


Instead of 16 , put in the last row number of your data range.


Narayan

 

[rbeccaas]

Hi Narayan,


While B-F... contain data, there is not data in each row of each column based on col A being sorted in time order. The result of time difference is now in the blank merged sections of each individual column. I have been doing it individually (sometimes thousands of rows !$#?).


I will test the formula you've provided as well as figure out how to upload a sample file as I am restricted from doing so at my current location.


Thanks much!