creating data points within collected data

  • Thread starter Thread starter Excel Data
  • Start date Start date
E

Excel Data

I have some time series data (every hour) but I need my data to line up to a
time series of every 15 minutes. Its from March to November its alot of data
not to have a short cut for. :) Thanks

For example: (what I have)
Time data
3/1/2009 0:00 99.12
3/1/2009 1:00 99.07
3/1/2009 2:00 99.06
3/1/2009 3:00 99
3/1/2009 4:00 98.98

But I need to copy the known data cells 99.12 into all the 0:00 times,
99.07 into 1:00 etc etc

Time Data (what I need)
3/1/2009 0:00 99.07
3/1/2009 0:15 99.07
3/1/2009 0:30 99.07
3/1/2009 0:45 99.07
3/1/2009 1:00
3/1/2009 1:15
3/1/2009 1:30
3/1/2009 1:45
3/1/2009 2:00
3/1/2009 2:15
3/1/2009 2:30
3/1/2009 2:45
3/1/2009 3:00
3/1/2009 3:15
3/1/2009 3:30
3/1/2009 3:45
3/1/2009 4:00
 
Suppose your data is in columns A and B. Put this in D2:

=MIN(A:A)

and format this cell as a date and time. Then put this formula in D3:

=D2+15/60/24

and copy this down as far as you need it - you will now have your date/
times in column D in 15 minute increments. Then put this in E2:

=VLOOKUP(D2,A:B,2)

and copy this down. This assumes that the data is sorted by time, as
your example suggests.

Hope this helps.

Pete
 
Back
Top