Hi there,
I have a dataset whereby I am trying to determine the busiest 'rolling' 60 minute periods. I can create this by clock hour, or 15, 30 minute periods etc, but I can't find how to achieve it for rolling periods. I know it is easy to do when the period is a standard 'thing' like day, or even hour, but my dataset is down to minute level.
Please see my data sample attached. I have entries that could vary by a minute, not just every 5 min as shown.Rolling60minsample.xlsx
Can try something like this:
Rolling60minsample - Copy.xlsx
<<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts
Great that it worked for you. Word of caution is just to take care if it rolls over midnight - in which case you can add days into the calculation too. But no need to overcomplicate if not required for your use case.
Ah, good point. I will be looking at data across the full 24h spectrum so e.g. in column G, if I have a RH Time Start of e.g. 23:50 then that needs to capture data 23:50 to 00:49. I need to extend the formula to encompass this.
Struggling to work out the syntax at this hour so will look again tomorrow. If you have any quick pointers on how to extend, that would be amazing!
Two approaches depending on your needs.
1) If you want to treat your dataset as being the same each day, then you just add logic so that from 11pm onwards, look at the start of day data again. i.e. for 23:15-00:14, use the existing formula to pick up 23:15-23:59, then add from 00:00-00:14 from the start of your list again.
Cell H2 becomes:Formula:
Please Login or Register to view this content.
2) If it's different data for each day, then you can use the similar approach by combining the date and time and formatting the TimeToVal as. You then also need to add days to your rolling hour analysis columns and logic too. Can help tomorrow if you go down this route and need some helpFormula:Please Login or Register to view this content.
Many thanks again!
Option 1 fits the use case perfectly. Saved me so much time figuring out the format.
Diamond, thank you.
Hello again, hoping AskMeAboutExcel is on here and able to look at this for me again. Sorry!
I have an extended version now of my previous issue whereby there are multiple dates as well as times to sort through. So in the previous example, a time series column ahead of the time column, with year-mo-da format. [Edited to add attachment now]
Can you possibly extend the formula to look at the busiest 60 minute rolling window across the whole time series? I've tried a few versions but not really getting past the formula looking at the hours (mostly) only.Rolling60minsample.xlsx
Last edited by Notsoeasy; 03-13-2023 at 05:00 PM. Reason: Add attachment
not sure if the sequence formula will work with your version of excel - if it does then the first option will be fine, but if not I've hardcoded a v2 that you can replicate
Depending how big your dataset is, there is a sensible limit to how many days you can study with this approach due to the number of rows involved. If a large number of dates, you might want to try some smarter formula approaches / powerquery
Rolling60minsample_v2 - Copy.xlsxRolling60minsample_v1 - Copy.xlsx
Many thanks yet again!
I did manage to lock up excel the first time round (it did warn me...), I think the Sequence formula is what slowed it all down?
My list is for a calendar year however I have multiple rows of same date in the raw data which I'm assuming expands the computation. I revised down a look up list to minimise this (365/366 rows for the dates) and this seemed to improve operation.
Typically I only need to look at a year at a time so this works well, and I've copy pasted for a range of years now so have those values on hand to apply to any future analysis.
Thank you.
For a very large set of dates it's likely that Sequence is the culprit for slowing it down.
Great that this works for you. An alternative to having 10's of thousands of rows is to have a table with dates along the top, minutes on the bottom - the calculation speed will be similar but it makes it a little more manageable to view.
Rolling60minsample_v4 - Copy.xlsx
It's still easy to find the MAX value, but finding the corresponding time through formulas is more complex (although you can manually search for the Max result to see the date/time)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks