how to re-divide the data at the same time interval

[wetlander]

I have 2 columns of data (value and time):

for example:

15 4/2/08 13:00
4 4/2/08 19:00
7 4/5/08 12:00
13 4/9/08 3:00

They are continuous data. so I want to divid the value into hourly data as follows.

15 4/2/08 13:00
? 4/2/08 14:00
? 4/2/08 15:00
. .
. .
? 4/9/08 2:00
13 4/9/09 3:00



Is there any way I could do it?

[martindwilson]

yes but date/ times are difficult to match, something to do with excels rounding.
with your data in column a and b with headers in column d cell d2 put your first date time say 1/31/2008 00:00
then in the cell below put the next increment
1/31/2008 01:00
select both and drag down
this will give you steps of one hour
now in column c cell c2 put this array formula
=INDEX($A$2:$B$500,MATCH(TEXT(D2,"mm/dd/yyyy hh:mm"),TEXT($B$2:$B$500,"mm/dd/yyyy hh:mm"),0),1)
click on formula in formula bar and do ctrl+shift+enter at the same time
{} will appear around it so it looks like this
{=INDEX($A$2:$B$500,MATCH(TEXT(D2,"mm/dd/yyyy hh:mm"),TEXT($B$2:$B$500,"mm/dd/yyyy hh:mm"),0),1))}
drag this formula down as far as required


if you dont want to see a loaD of #N/A
USE
=IF(ISNA( MATCH(TEXT(D2,"mm/dd/yyyy hh:mm"),TEXT($B$2:$B$500,"mm/dd/yyyy hh:mm"),0)),"", INDEX($A$2:$B$500,MATCH(TEXT(D2,"mm/dd/yyyy hh:mm"),TEXT($B$2:$B$500,"mm/dd/yyyy hh:mm"),0),1))
again entered with ctrl+shift+enter

[DonkeyOte]

I guess you could set the first time (D2) to be the MIN of Column B

D2: =MIN(B:B)

Then D3 onwards

D3: =D2+"01:00:00"
copied down

Then you could use LOOKUP in C

C2: =LOOKUP(D2+"00:00:01",B:B,A:A)
copied down as far as required

Pending volume of rows you're talking about it may make more sense to use VBA ?

[zbor]

wrong idea : )

[wetlander]

I think my question posted earlier was misunderstood, so I decide to post it more clearly again to see if there's help.

If I have 4 sets of data (value, time) with a linear relationship between each value as follows:

4 4/2/08 7:00
12 4/2/08 11:00
3 4/2/08 14:00
11 4/2/08 16:00

what formula can I use to change the data above into the hourly data like below:

4 4/2/08 7:00
6 4/2/08 8:00
8 4/2/08 9:00
10 4/2/08 10:00
12 4/2/08 11:00
9 4/2/08 12:00
6 4/2/08 13:00
3 4/2/08 14:00
7 4/2/08 15:00
11 4/2/08 16:00

Thanks for the help!

[shg]

Threads merged.

There's no need to start a new thread; just provide a better explanation and continue.

[martindwilson]

just for future use can you or anyone if giving dates use examples of days >12 as i for one often mistake 7/8 for 8/7 and its not clear what is wanted

[martindwilson]

the example i posted will give

Please Login or Register  to view this content.

now how you fill in the missing values is a different matter

[DonkeyOte]

There must be a better way but based on my interpretation:

[martindwilson]

now thats better!