Spreadsheet question: maximum simulanteous events
May 21, 2021 2:34 PM Subscribe
I have a spreadsheet for orders from a restaurant.
It lists the start time in one column and the stop time in another column.
It’s massive (covers an entire year).
I would like to find, for every hour (or every 12 hours, or any other interval), the maximum number of orders that were open at the same time. Ie orders that were started, but not stopped.
How would I do this in excel?
Can you clarify what you mean by, "for every hour (or every 12 hours, or any other interval)" ?
Are you trying to generate a big table of every 12-hour (or whatever interval) period throughout the year, showing the maximum number of open orders in each period, e.g.
January 1st, 0:00-11:59, 11 orders
January 1st, 12:00-23:59, 9 orders
January 2nd, 0:00-11:59, 15 orders
etc.....
Or, maybe you're looking for the maximum number of open orders between 0:00 and 11:59 on any given Monday, Tuesday, etc?
Something else?
posted by jon1270 at 4:40 PM on May 21, 2021
Are you trying to generate a big table of every 12-hour (or whatever interval) period throughout the year, showing the maximum number of open orders in each period, e.g.
January 1st, 0:00-11:59, 11 orders
January 1st, 12:00-23:59, 9 orders
January 2nd, 0:00-11:59, 15 orders
etc.....
Or, maybe you're looking for the maximum number of open orders between 0:00 and 11:59 on any given Monday, Tuesday, etc?
Something else?
posted by jon1270 at 4:40 PM on May 21, 2021
Assuming your date syntax is understandable by excel you could do this as a pivot table or using the data analysis histogram function.
If you date syntax doesn't work this becomes way more annoying
posted by JPD at 5:46 PM on May 21, 2021 [1 favorite]
If you date syntax doesn't work this becomes way more annoying
posted by JPD at 5:46 PM on May 21, 2021 [1 favorite]
I’m just thinking out loud, but assuming your time intervals vary, I’d find the smallest increment (1 hour? 5 minutes?). In another worksheet, you can list each time slot, and use some clever countif statements to count how many slots contain that time block. Then it’s just finding the max.
posted by Valancy Rachel at 5:54 PM on May 21, 2021
posted by Valancy Rachel at 5:54 PM on May 21, 2021
yuwtze's approach to compute the number of open orders sounds good to me:
> take the column with the end times, and move them to the bottom of the start times column, and in a new column, put +1 in each row that has a start time, and -1 in each row that has an end time. Then sort by time, and in a third column, calculate a running total of all the +1s and -1s. That would give you, for every point in the sheet, the number of open orders.
that should be a great starting point unless the timestamps lack a date - if you've just got time of day but not the date you'd need to figure out how to add that in first so that you can order the events correctly, otherwise events from different days will get all mixed up.
once you've got a table of data with the two columns (datetime, number of open orders) following yuwtze approach, before transforming it any further you could try producing a graph. e.g. graph number of open orders on the y axis as a function of the datetime on the x axis.
that may be quite informative to stare at - you might be able to figure out the maximums by just eyeballing the chart
if there's too much information and you're interested in e.g. how the open order count varies from day to day during a week you could try doing a chart with 52 overlapping plots, one per week of the year -- maybe you'd need to define a new column that has an incrementing week counter and figure out how to coax excel into partitioning the data by the week counter
posted by are-coral-made at 6:03 PM on May 21, 2021 [1 favorite]
> take the column with the end times, and move them to the bottom of the start times column, and in a new column, put +1 in each row that has a start time, and -1 in each row that has an end time. Then sort by time, and in a third column, calculate a running total of all the +1s and -1s. That would give you, for every point in the sheet, the number of open orders.
that should be a great starting point unless the timestamps lack a date - if you've just got time of day but not the date you'd need to figure out how to add that in first so that you can order the events correctly, otherwise events from different days will get all mixed up.
once you've got a table of data with the two columns (datetime, number of open orders) following yuwtze approach, before transforming it any further you could try producing a graph. e.g. graph number of open orders on the y axis as a function of the datetime on the x axis.
that may be quite informative to stare at - you might be able to figure out the maximums by just eyeballing the chart
if there's too much information and you're interested in e.g. how the open order count varies from day to day during a week you could try doing a chart with 52 overlapping plots, one per week of the year -- maybe you'd need to define a new column that has an incrementing week counter and figure out how to coax excel into partitioning the data by the week counter
posted by are-coral-made at 6:03 PM on May 21, 2021 [1 favorite]
Can't you just make a column that counts how many orders are open at the time of each order's start? Like seen here in this example spreadsheet I made.
posted by aubilenon at 11:32 PM on May 21, 2021 [1 favorite]
posted by aubilenon at 11:32 PM on May 21, 2021 [1 favorite]
Oh rats, I didn't see the "for every hour" thing. So I updated my spreadsheet, instead of abusing the edit window. Now it does what I said, and then computes the maxima for each interval. I believe it is correct!
posted by aubilenon at 11:56 PM on May 21, 2021
posted by aubilenon at 11:56 PM on May 21, 2021
Because you mention that this is a years worth of orders: this is the exact type of use case for the power pivot tools in Excel. You would select your table, go to the data tab, and add that table/range to that data model. Then, you would create a measure that gives the max of orders open but not yet closed.
Your search term de jour is “time intelligence”. You will probably need to create a date/time table, depending on how you want to break down your data, and those search results will explain how to set that up. You will also run into the term “DAX”; that is the formula language that you will use to write the calculation.
It sounds intimidating, and admittedly time intelligence is the trickier part of power pivot, but doing aggregation calculations on large data sets is exactly what it’s for, and it’s a really useful skill if you’re doing spreadsheet work on a regular basis.
posted by philosophygeek at 2:46 AM on May 22, 2021
Your search term de jour is “time intelligence”. You will probably need to create a date/time table, depending on how you want to break down your data, and those search results will explain how to set that up. You will also run into the term “DAX”; that is the formula language that you will use to write the calculation.
It sounds intimidating, and admittedly time intelligence is the trickier part of power pivot, but doing aggregation calculations on large data sets is exactly what it’s for, and it’s a really useful skill if you’re doing spreadsheet work on a regular basis.
posted by philosophygeek at 2:46 AM on May 22, 2021
Pick your Start and End times.
Pick all events that started before Start that didn't end before Start; These are the open cases.
Add the events that started after the Start date and before the End date; These are the cases opened and either closed or not.
Count the number of events that match those two conditions.
How do you want to handle events that span across a single or multiple boundaries of your time-slice?
posted by zengargoyle at 3:33 AM on May 22, 2021
Pick all events that started before Start that didn't end before Start; These are the open cases.
Add the events that started after the Start date and before the End date; These are the cases opened and either closed or not.
Count the number of events that match those two conditions.
How do you want to handle events that span across a single or multiple boundaries of your time-slice?
posted by zengargoyle at 3:33 AM on May 22, 2021
Oops I realized I was working in "Copy of sheet 1" so if you looked at my spreadsheet but not all the tabs, you would have missed the part where it has time intervals. Fixed.
posted by aubilenon at 7:23 AM on May 22, 2021
posted by aubilenon at 7:23 AM on May 22, 2021
3 Ways to Group Times in Excel
2 Quick Ways to Group Time in Excel Pivot Tables
posted by oceano at 9:27 AM on May 22, 2021
2 Quick Ways to Group Time in Excel Pivot Tables
posted by oceano at 9:27 AM on May 22, 2021
Response by poster: Yuwtze's brute force approach worked wonderfully. Thanks so much!
posted by cacofonie at 6:35 PM on May 29, 2021
posted by cacofonie at 6:35 PM on May 29, 2021
This thread is closed to new comments.
I'm sure that there's better ways to do this, especially if you need to perform this analysis repeatedly, but for a one-off, this is probably how I'd start.
posted by yuwtze at 4:13 PM on May 21, 2021 [2 favorites]