Let us say there are three columns: A, B, and C.

Column A contains time formatted to [hh]:mm:ss and goes from 0:00:00 to
21:20:00. There are an arbitrary number of entries in this column.

Column B contains time formatted to [hh]:mm:ss and starts from 0:00:00
and ends at 21:30:00. -It increases in 15 minute intervals exactly.-

Column C contains data that is attached to Column A on the same row.

What I want is to make it that Column A's values match themselves to
Column B's values. I.E. For values of A between 00:00:00 and Column B
row 2 (00:15:00) I want the values of A (and therefore C) to line up
next to Column B row entry 00:00:00.

This isn't a very clear explanation. I will explain it again, and maybe
you can piece together what I mean.

There are many columns: Cumulative Time, 15 Minute Intervals and
several columns attached to the Cumulative Time column. The cumulative
time column is effectively a timestamp.

I want to sort my data so that the data values timestamped between,
say, 0 minutes and 15 minues will be together. The problem is, some 15
minute intervals have no entries, so I want a blank in the Cumulative
Time column next to the 15 minute interval which contains no time
points.

I will attach the spreadsheet. The columns of interest are B
(Cumulative Time) and C (15 minute intervals). All the other columns
should be associated with column A.

This will allow me to make 15 minute averages of my data (the columns N
to Q).

Thank you in advance. I really hope someone can help me, because this
problem has me really stumped. As you can see from the spreadsheet,
I've been tortuously grappling with this problem for a while!

Thanks,

Adam


+-------------------------------------------------------------------+
|Filename: Test Rat 080206-090206 Complete.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4592 |
+-------------------------------------------------------------------+

--
A S-D
------------------------------------------------------------------------
A S-D's Profile: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Hi there,

Looked at spreadsheet but cannot figure out what you want to do,
sorry.

You can make a lookup table so that any time in column B is assigned a
code number linked to a particular 15 minute slot.

The top of it would look like this

00:00:00 code1
00:15:00 code2
00:30:00 code3

say you have a time of 00:17:11 in cell A1 and the lookuptable is
called table1

=vlookup(A1,table1,2) returns code2

will wait for more info from you


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Thank you for your consideration, Robert111.

I don't know what a vlookup table is, I'm afraid.

Let me try to explain a little clearer, as I think your solution falls
into problems when an interval would have no entries.

I have data from a device. This device records the RApid Turn of some
apparatus, through a varying voltage. Each time the voltage changes
(i.e. the device detects a turn) an entry is made with the appropriate
timestamp from time zero which is the start of the recording.

So..

-5.00 Volts 02:56:41
0.00 Volts 02:56:44
+5.00 Volts 02:57:03

I want to separate my data into 15 minute blocks, relative to the
start. How do I make it so that the timestamp maps itself to the 15
minute intervals? In some intervals, there was no activity at all, so
that interval should have no corresponding timestamps.

Is this even possible in Excel?


--
A S-D
------------------------------------------------------------------------
A S-D's Profile: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Hi,
Given the following data from your spreadsheet , how do you want it
changed? Like Robert, I am struggling to see how you want it formatted.

Cumulative
Time 15 minute
intervals Voltage of turn.
0:00:00 00:00:00 -0.06
0:18:44 00:15:00 5.02
0:18:45 00:30:00 -0.02
1:16:59 00:45:00 2.52
1:17:00 01:00:00 -0.04


"A S-D" wrote:


Thank you for your consideration, Robert111.

I don't know what a vlookup table is, I'm afraid.

Let me try to explain a little clearer, as I think your solution falls
into problems when an interval would have no entries.

I have data from a device. This device records the RApid Turn of some
apparatus, through a varying voltage. Each time the voltage changes
(i.e. the device detects a turn) an entry is made with the appropriate
timestamp from time zero which is the start of the recording.

So..

-5.00 Volts 02:56:41
0.00 Volts 02:56:44
+5.00 Volts 02:57:03

I want to separate my data into 15 minute blocks, relative to the
start. How do I make it so that the timestamp maps itself to the 15
minute intervals? In some intervals, there was no activity at all, so
that interval should have no corresponding timestamps.

Is this even possible in Excel?


--
A S-D
------------------------------------------------------------------------
A S-D's Profile: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Again, thanks for looking at this problem. I wish I could explain this
more simply!

Bryan, the column C is merely the whole testing period split into 15
minute intervals. It is what I want my data ordered by:

Cumulative
Time 15 minute
intervals Voltage of turn.
0:00:00 00:00:00 -0.06
0:18:44 00:15:00 5.02
0:18:45 00:30:00 -0.02
1:16:59 00:45:00 2.52
1:17:00 01:00:00 -0.04

Should become:



Code:
--------------------

15 Minute Intervals // Cumulative Time // Voltage of Turn (rest of data)

00:00:00 [EMPTY] 0
00:15:00 0:18:44 5.02
[EMPTY] 0:18:45 -0.02
00:30:00 [EMPTY] 0
00:45:00 [EMPTY] 0
01:00:00 [EMPTY] 0
01:15:00 1:16:59 2.52
[EMPTY] 1:17:00 -0.04

--------------------


Does that help?


--
A S-D
------------------------------------------------------------------------
A S-D's Profile: http://www.excelforum.com/member.php...o&userid=33227
View this thread: http://www.excelforum.com/showthread...hreadid=530452

So, as per sheet 2 of the attached is a count of items, is that how you
would see your data?

using
=COUNTIF(Test1!C2:C409,D2)

--

A S-D Wrote:
Again, thanks for looking at this problem. I wish I could explain this
more simply!

Bryan, the column C is merely the whole testing period split into 15
minute intervals. It is what I want my data ordered by:

Cumulative
Time 15 minute
intervals Voltage of turn.
0:00:00 00:00:00 -0.06
0:18:44 00:15:00 5.02
0:18:45 00:30:00 -0.02
1:16:59 00:45:00 2.52
1:17:00 01:00:00 -0.04

Should become:



Code:
--------------------

15 Minute Intervals // Cumulative Time // Voltage of Turn (rest of data)

00:00:00 0:00:00 -0.06
00:15:00 0:18:44 5.02
[EMPTY] 0:18:45 -0.02
00:30:00 [EMPTY] 0
00:45:00 [EMPTY] 0
01:00:00 [EMPTY] 0
01:15:00 1:16:59 2.52
[EMPTY] 1:17:00 -0.04

--------------------


Does that help?


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530452

Hi,

I cannot understand what you are trying to do with the 15 minute
period, if you are trying to asses the happenings of each 15 minute
period of items in column A, then the attached with a new column C
might help, but there are (15 minute) periods where there is no
activity asin between rows 4 and 5, and other 15 min periods where
there is much activity asin rows 5 to 18.

From the newly inserted column C, subtotals could be drawn for the 15
minute periods, and these could be the subject of a vlookup looking on
time to match up to a complete table of 15 minute increments if
required, however, your original 15 minute lots do not appear to be
'day' specific, should they be?

Hope this helps

--

A S-D Wrote:
Let us say there are three columns: A, B, and C.

Column A contains time formatted to [hh]:mm:ss and goes from 0:00:00 to
21:20:00. There are an arbitrary number of entries in this column.

Column B contains time formatted to [hh]:mm:ss and starts from 0:00:00
and ends at 21:30:00. -It increases in 15 minute intervals exactly.-

Column C contains data that is attached to Column A on the same row.

What I want is to make it that Column A's values match themselves to
Column B's values. I.E. For values of A between 00:00:00 and Column B
row 2 (00:15:00) I want the values of A (and therefore C) to line up
next to Column B row entry 00:00:00.

This isn't a very clear explanation. I will explain it again, and maybe
you can piece together what I mean.

There are many columns: Cumulative Time, 15 Minute Intervals and
several columns attached to the Cumulative Time column. The cumulative
time column is effectively a timestamp.

I want to sort my data so that the data values timestamped between,
say, 0 minutes and 15 minues will be together. The problem is, some 15
minute intervals have no entries, so I want a blank in the Cumulative
Time column next to the 15 minute interval which contains no time
points.

I will attach the spreadsheet. The columns of interest are B
(Cumulative Time) and C (15 minute intervals). All the other columns
should be associated with column A.

This will allow me to make 15 minute averages of my data (the columns N
to Q).

Thank you in advance. I really hope someone can help me, because this
problem has me really stumped. As you can see from the spreadsheet,
I've been tortuously grappling with this problem for a while!

Thanks,

Adam


+-------------------------------------------------------------------+
|Filename: Lab Rat 080206-090206 Complete.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4593 |
+-------------------------------------------------------------------+

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=530452