Help with date time function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a rather large spreadsheet that I am making into a Chart
The data was captured every every 5 seconds for a 24hr period. I need to "condense" this data into a range of 1 min. So I basically need take the 1st row of data and then skip the next 10rows then get then next row and skip the next range of time
Due to the possibility that the rows will not be all 10 apart, I need to use a date/time function that will check if the previouse row is part of the last min or the next min

Data sampl
4/29 8:16:02 AM Keep >>
4/29 8:16:07 AM Ski
4/29 8:16:12 AM Ski
4/29 8:16:17 AM Ski
4/29 8:16:22 AM Ski
4/29 8:16:27 AM Ski
4/29 8:16:32 AM Ski
4/29 8:16:37 AM Ski
4/29 8:16:42 AM Ski
4/29 8:16:47 AM Ski
4/29 8:16:52 AM Ski
4/29 8:16:57 AM Ski
4/29 8:17:02 AM Keep >>>
4/29 8:17:07 A
4/29 8:17:12 A
4/29 8:17:17 A
4/29 8:17:22 A
4/29 8:17:27 A
4/29 8:17:32 A
4/29 8:17:37 A
4/29 8:17:42 A
4/29 8:17:47 A
4/29 8:17:53 A
4/29 8:17:58 A
 
Hi

If your data is in A1:A50, try this:
In B1
=A1
In B2
=OFFSET($A$1,(ROW()-1)*10,0)
and fill down

--
Andy.


jc said:
I have a rather large spreadsheet that I am making into a Chart.
The data was captured every every 5 seconds for a 24hr period. I need to
Click to expand...
"condense" this data into a range of 1 min. So I basically need take the
1st row of data and then skip the next 10rows then get then next row and
skip the next range of time.
Due to the possibility that the rows will not be all 10 apart, I need to
Click to expand...
use a date/time function that will check if the previouse row is part of the
last min or the next min.
 
Hi jc
With the dates starting in A1insert a new column B and enter this formula in B2

=IF(MINUTE(A1)=MINUTE(A2),NA(),A2

Then copy down to the bottom. This can be done real real quick by double clicking on the fill handle

Now column B will have the first reading in every minute, the first line will need to be done manually. All other reading will have an #N/A error. You can now graph directly off this table using column B instead of A, because Excel will ignore the lines with #N/A errors

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- jc wrote: ----

I have a rather large spreadsheet that I am making into a Chart
The data was captured every every 5 seconds for a 24hr period. I need to "condense" this data into a range of 1 min. So I basically need take the 1st row of data and then skip the next 10rows then get then next row and skip the next range of time
Due to the possibility that the rows will not be all 10 apart, I need to use a date/time function that will check if the previouse row is part of the last min or the next min

Data sampl
4/29 8:16:02 AM Keep >>
4/29 8:16:07 AM Ski
4/29 8:16:12 AM Ski
4/29 8:16:17 AM Ski
4/29 8:16:22 AM Ski
4/29 8:16:27 AM Ski
4/29 8:16:32 AM Ski
4/29 8:16:37 AM Ski
4/29 8:16:42 AM Ski
4/29 8:16:47 AM Ski
4/29 8:16:52 AM Ski
4/29 8:16:57 AM Ski
4/29 8:17:02 AM Keep >>>
4/29 8:17:07 A
4/29 8:17:12 A
4/29 8:17:17 A
4/29 8:17:22 A
4/29 8:17:27 A
4/29 8:17:32 A
4/29 8:17:37 A
4/29 8:17:42 A
4/29 8:17:47 A
4/29 8:17:53 A
4/29 8:17:58 A
 
Back
Top