Concurrent event analysis
In the attached Excel file, I have the raw data in the worksheet "Raw data". I want to perform an analysis to get the number of concurrent events during a particular hour of a day. The resulting table should look like the one in the 2nd worksheet "Final data". Is there any Excel Formula or VB macro I can use to automate the analysis? Thanks.
| Attachment | Size |
|---|---|
| Sample analysis.xlsx | 12.91 KB |
| sample analysis v2.xlsx | 15.06 KB |
RE: Concurrent event analysis
Hi,
Here's some solution.
Firstly, if you can change sheet Final result to the following:
you can use this formula:
= SUM(('Raw data'!B$2:B$29 >= A2) * ('Raw data'!$C$2:$C$29 <= (A2 + TIME(0, 59, 0))), ('Raw data'!B$2:B$29 <= A2) * ('Raw data'!$C$2:$C$29 >= (A2 + TIME(0, 59, 0))), ('Raw data'!B$2:B$29 <= A2) * ('Raw data'!$C$2:$C$29 >= A2) * ('Raw data'!$C$2:$C$29 <= (A2 + TIME(0, 59, 0))), ('Raw data'!B$2:B$29 >= A2) * ('Raw data'!B$2:B$29 <= (A2 + TIME(0, 59, 0))) * ('Raw data'!$C$2:$C$29 >= (A2 + TIME(0, 59, 0))))
Note: This is array formula and you must press CTRL + SHIFT + ENTER instead only ENTER.
If you want to preserve the current view of sheet Final result:
replace all A2 into above formula with:
(DATEVALUE(A2 & "-" & B2 & "-" & C2) + TIMEVALUE(E2 & ":00:00"))
Best regards.
manny's solution
It's almost 99% correct. Thanks!
The only weird thing is that sometimes it gives incorrect value. See the attached "sample analysis v2.xlsx" file.
Formula correction
Hi,
I made a mistake. I defined the wrong intervals.
Just for clarity (corrections are bolded and underlined):
- this is the wrong formula:
= SUM(('Raw data'!B$2:B$29 >= A2) * ('Raw data'!$C$2:$C$29 <= (A2 + TIME(0, 59, 0))), ('Raw data'!B$2:B$29 <= A2) * ('Raw data'!$C$2:$C$29 >= (A2 + TIME(0, 59, 0))), ('Raw data'!B$2:B$29 <= A2) * ('Raw data'!$C$2:$C$29 >= A2) * ('Raw data'!$C$2:$C$29 <= (A2 + TIME(0, 59, 0))), ('Raw data'!B$2:B$29 >= A2) * ('Raw data'!B$2:B$29 <= (A2 + TIME(0, 59, 0))) * ('Raw data'!$C$2:$C$29 >= (A2 + TIME(0, 59, 0))))
- this is the correct formula:
= SUM(('Raw data'!B$2:B$29 >= A2) * ('Raw data'!$C$2:$C$29 <= (A2 + TIME(0, 59, 0))), ('Raw data'!B$2:B$29 < A2) * ('Raw data'!$C$2:$C$29 > (A2 + TIME(0, 59, 0))), ('Raw data'!B$2:B$29 < A2) * ('Raw data'!$C$2:$C$29 >= A2) * ('Raw data'!$C$2:$C$29 <= (A2 + TIME(0, 59, 0))), ('Raw data'!B$2:B$29 >= A2) * ('Raw data'!B$2:B$29 <= (A2 + TIME(0, 59, 0))) * ('Raw data'!$C$2:$C$29 > (A2 + TIME(0, 59, 0))))
To save you some time, here's the corrected formula from your example file:
= SUM(('Raw data'!B$2:B$5 >= (DATEVALUE(A2 & "-" & B2 & "-" & C2) + TIMEVALUE(E2 & ":00:00"))) * ('Raw data'!$C$2:$C$5 <= ((DATEVALUE(A2 & "-" & B2 & "-" & C2) + TIMEVALUE(E2 & ":00:00")) + TIME(0, 59, 0))), ('Raw data'!B$2:B$5 < (DATEVALUE(A2 & "-" & B2 & "-" & C2) + TIMEVALUE(E2 & ":00:00"))) * ('Raw data'!$C$2:$C$5 > ((DATEVALUE(A2 & "-" & B2 & "-" & C2) + TIMEVALUE(E2 & ":00:00")) + TIME(0, 59, 0))), ('Raw data'!B$2:B$5 < (DATEVALUE(A2 & "-" & B2 & "-" & C2) + TIMEVALUE(E2 & ":00:00"))) * ('Raw data'!$C$2:$C$5 >= (DATEVALUE(A2 & "-" & B2 & "-" & C2) + TIMEVALUE(E2 & ":00:00"))) * ('Raw data'!$C$2:$C$5 <= ((DATEVALUE(A2 & "-" & B2 & "-" & C2) + TIMEVALUE(E2 & ":00:00")) + TIME(0, 59, 0))), ('Raw data'!B$2:B$5 >= (DATEVALUE(A2 & "-" & B2 & "-" & C2) + TIMEVALUE(E2 & ":00:00"))) * ('Raw data'!B$2:B$5 <= ((DATEVALUE(A2 & "-" & B2 & "-" & C2) + TIMEVALUE(E2 & ":00:00")) + TIME(0, 59, 0))) * ('Raw data'!$C$2:$C$5 > ((DATEVALUE(A2 & "-" & B2 & "-" & C2) + TIMEVALUE(E2 & ":00:00")) + TIME(0, 59, 0))))
I'm sorry that I misled you...
Best regards.
Yes it's fine to add these
Yes it's fine to add these new columns
what will the formula looks like by using countif? Thanks!
Concurrent event analysis
How does it work? You extract year, month, day and hour in separate columns. Then you count rows that satisfy all conditions (same year, month, day and hour) and counts them up. I have also tried to do it without additional columns (SUBSTITUTE function and array formula), but I didn't get it. So...
In "Raw data" sheet add four columns in D, E, F and G (Year, Month, Day and Hour). Supposing your date and time are in B2 cell:
In D2 enter: =YEAR(B2),
In E2 enter: =MONTH(B2),
In F2 enter: =DAY(B2), and
In G2 enter: =HOUR(B2).
Your will get year, month, day and hour of your B2 data. Copy formula down to the end of the table.
Now, go to "Final Result" sheet.
Select F2 cell (No. of concurrent events).
Copy the following formula:
=COUNTIFS('Raw data'!$D$2:$D$29;'Final result'!A2;'Raw data'!$E$2:$E$29;'Final result'!B2;'Raw data'!$F$2:$F$29;'Final result'!C2;'Raw data'!$G$2:$G$29;'Final result'!E2)
Copy formula down.
Concurrent event analysis
hmm seems it's not that straight forward
In the Raw Data Tab, Column B is the Start time of a event and Column C is the Resolved Time (End Time) of the event.
Now let's look at the first 3 events:
Event 1: 00:!2 - 00:48
Event 2: 02:44 - 06:40
Event 3: 03:04 - 03:04 (end time is less than 1 min)
And if we look at the timeline, we know that these 3 events are still active during these hours:
00:00 - 00:59 => Event 1 is active. Concurrent active events is 1
01:00 - 01:59 => No active event. Concurrent active events is 0
02:00 - 02:59 => Event 2 is active. Concurrent active events is 1
03:00 - 03:59 => Event 2 and 3 are active. Concurrent active events is 2
04:00 - 04:59 => Event 2 is active. Concurrent active events is 1
05:00 - 05:59 => Event 2 is active. Concurrent active events is 1
06:00 - 06:59 => Event 2 is active. Concurrent active events is 1
Cells F2 to F5 in "Final result" tab are derived from the above logic (results are 1, 0, 1, 2). With your formula, the results are 1, 0, 1, 1. Sounds like we need to look at both Start Time and Resolved Time (End Time) and have some script or formula to check whether the event period falls within a specific hour and count them. My head explodes everytime I think about it :)
Concurrent event analysis
Ah, yes, I misunderstood your initial request. You need number of concurrent active events during one hour. This includes events started in earlier hour and extending to the next hours as well. This requires different formula, but I will figure it out and send it tomorrow to you.
My head explodes too now. Bad news: I haven't finished yet. Good news: I am on good way to solution. Can you just confirm my assumption about your need: You need number of concurrent active events during one hour. This includes events started in earlier hour and extending to the events started at particular hour and finished later. Is this correct?
Yes. There are overlapping
Yes. There are overlapping events and I need to know how many events that are still happening within a specific hour. Thanks a lot for your help!
Concurrent event analysis
Let me know if it is convenient to you to add four columns to raw data (year, month, date and hour). We could extract year, month, date and hour from original columns by formula and then use COUNTIF function to get result in final table quickly.
If it is problem to add these columns to raw data, then we need to figure it out in different way.