Hourly Avarages

Question

Hi&nbsp;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).&nbsp;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.&nbsp;Hope someone has the answer 🙂&nbsp;Column F should also contain the date, my mistake. Column G is what I am trying to achieve.

hansvogelaar · Answer

JonasAR1&nbsp;
Why is the value for 8:59 included in the result for 9:00?

jonasar1 · Answer

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!

hansvogelaar · Answer

JonasAR1&nbsp;
No, I don't understand.

jonasar1 · Answer

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.

hansvogelaar · Answer

JonasAR1&nbsp;
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.

Forum Discussion

Hourly Avarages

5 Replies

Resources