Forum Discussion
Hourly Avarages
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 (Lik...
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!
No, I don't understand.
- 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.
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.