May 24 2022 03:48 AM - edited May 24 2022 03:55 AM
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.
May 24 2022 04:09 AM
Why is the value for 8:59 included in the result for 9:00?
May 24 2022 04:12 AM
May 24 2022 06:13 AM
May 25 2022 04:17 AM
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.