Splitting hourly data into 1/4 hourly data

**freethinker** · 11-09-2012, 05:30 AM

Hi,

I am working with some flow data which I have in hourly time steps. I want to split this into 15 minute time steps with some simple linear interpolation between the hourly values. I know I could work down the data interpolating between each hour, but is there a faster way? The rate of increase/decrease in flow changes each hour. Is there a better way to set out my data to do this? The data set is quite large and at the moment is set up like this:

Time	Flow
0900	3.4
0915
0930
0945
1000	5.2
1015
1030
1045
1100	6.0

Thanks for any help

**zbor** · 11-09-2012, 05:57 AM

Do you mean like this:

**freethinker** · 11-09-2012, 06:12 AM

Thank you, this is exactly what I wanted.

Please could you try and explain the formula you have used with words? Just so I can understand what excel is doing.

Thanks again

**zbor** · 11-09-2012, 06:25 AM

Now when I try to analize it for you I see that this is not good approach because it works only in ascending order of values in B column...
Need to take a closer look.

**freethinker** · 11-09-2012, 06:26 AM

ahh yes, the data in the 2nd column can go up or down

**zbor** · 11-09-2012, 06:28 AM

This should do a trick.

Formula:

Please Login or Register  to view this content.

**zbor** · 11-09-2012, 06:30 AM

Explanation:

Formula:

Please Login or Register  to view this content.

this part of the formula

Formula:

Please Login or Register  to view this content.

will take a step between two values. Notice that it always need to be every 4th cell (that's why table can not start before 4th row) and all other values must be blank. (This is very specific issue, it's important to tell if you have some other requirements).

Now, when you got a step (+0.45 in first part, -1.8 in second) you need to add it to the previous value (C4) unless B5 already has a value (B5="")*.
If it has a value IF(B5="", will result FALSE and you need to take that value ,B5) as a result.

**freethinker** · 11-09-2012, 07:51 AM

Thank you

Would it be ok to take out this part of the formula underlined now?

=IF(B5="",C4+(B5="")*(MAX(B6:B9)-MAX(B2:B5))/4,B5)

**zbor** · 11-09-2012, 08:01 AM

Actually it would becase it' remain from earlier approach when I wasn't look at the B5 with IF function.
now when I already check that B5 is blank then you can leave that part out.
Nice spotted.