Hourly Avarages

[JonasAR1]

Hi 

I have a an issue that I cannot resolve in excel.

I have a list of time stamps (Column A) that shows when a new value starts (value in column B).

What I need is a list of 1 hour intervals (Like in column F) over 24 hours with the average value of column b in that hour (Like in Column G).

As you can see, values carry over from the day before (row 25), so that is the value in the first ~8:30 hours is 135, but the hour that starts 08:00:00 also have rows 24, 23, 22, 21 and one minute of row 20.

In cell G10 I have tried to explain how the equation would look if it was done by hand.

This data is 1 of 7 days I have, so doing it by hand would take a long time. 

Hope someone has the answer :) 

Column F should also contain the date, my mistake. Column G is what I am trying to achieve.

[#2]

@JonasAR1 

Why is the value for 8:59 included in the result for 9:00?

[#3]

because the first new value in 09:00 - 10:00 starts at 09:30, so untill then, the value is still the one that started at 08:59, does that make sense? otherwise I will try to make my self clear in another way :) Thank you for you interest!

[#4]

@JonasAR1 

No, I don't understand.

[#5]

okay, these numbers are the watts/hour for a unit. At 08:59 the system scales so it now uses 270 watts/hour (in this example it also did before, but that is not important). Then at 09:30 it changes again (again to a setting that uses 270 watt/hour). In the start of the 09:00 - 10:00 period, it is still in the setting that it was changed to at 08:59 until 09:30. So half of the period 09:00 - 10:00 it uses the setting that started at 08:59. Hope this is a better explanation.

[#6]

@JonasAR1 

Thanks. I tried to create a formula using built-in functions, but it became horribly complicated. Perhaps it can be done more easily using new functionality available to Office Insiders, but I ended up creating a custom VBA function.

See the attached sample workbook.