Concurrent situations

[Gcook90]

Hi all,

Working on a spreadsheet which shows telephone calls:

F column is Start time of the call (e.g. 21/05/2024 12:30)
J column is End time of the call (e.g. 21/05/2024 12:35)
K column is duration of the call in minutes and seconds (e.g. 5m 20s)

Is there a way to work out how many calls took place concurrently at the same time as each call based on this data?

Thanks in advance!

 

[Gcook90]

It's just to work out how many simultaneous calls are taking place on a very large dataset.
Your formula was working out how many are taking place at some point during the call, but not the same time.

Anyway, thank you for trying. If anyone else can help, it would be much appreciated.

 

[Vogateer]

The only solution I could come up with was to generate a sequence of time from the start to the end of these calls, with a step value of one second (though I guess you could change that to be less accurate), then test to see how many calls would be taking place during each second. Then we find the maximum number of calls for any specific call's range of time. This seems inefficient, but I'm not clever enough to come up with a better strategy.

Your columns start with F, but for this formula the columns start with A. I think the formula below is giving you what you want, though I did see at least one difference compared to what you showed above. For the call that started at 24/04/2024 08:36:54, you got 3 calls, but my formula is showing 4. Even if my formula isn't quite right, I think it might be a step in the right direction.

=MAX(LET(start,$A2,end,$B2, all_start, $A$2:$A$37, all_end, $B$2:$B$37,time_seq,SEQUENCE((end-start)*24*60*60,1,start,1/(24*60*60)),COUNTIFS(all_start,"<="&time_seq,all_end,">="&time_seq)))

 

[Vogateer]

It's just to work out how many simultaneous calls are taking place on a very large dataset.
Your formula was working out how many are taking place at some point during the call, but not the same time.

Anyway, thank you for trying. If anyone else can help, it would be much appreciated.

I wanted to check in and see if my formula above was giving you what you want. I wanted to see whether that formula would work on a large dataset.

 

[Gcook90]

The only solution I could come up with was to generate a sequence of time from the start to the end of these calls, with a step value of one second (though I guess you could change that to be less accurate), then test to see how many calls would be taking place during each second. Then we find the maximum number of calls for any specific call's range of time. This seems inefficient, but I'm not clever enough to come up with a better strategy.

Your columns start with F, but for this formula the columns start with A. I think the formula below is giving you what you want, though I did see at least one difference compared to what you showed above. For the call that started at 24/04/2024 08:36:54, you got 3 calls, but my formula is showing 4. Even if my formula isn't quite right, I think it might be a step in the right direction.

=MAX(LET(start,$A2,end,$B2, all_start, $A$2:$[COLOR=rgb(251, 160, 38)]A$37[/COLOR], all_end, $B$2:$[COLOR=rgb(235, 107, 86)]B$37[/COLOR],time_seq,SEQUENCE((end-start)*24*60*60,1,start,1/(24*60*60)),COUNTIFS(all_start,"<="&time_seq,all_end,">="&time_seq)))

Thanks Vogateer! That worked - did take a while to run though! Changed the highlighted above to match the dataset. Thanks so much for your help!
It doesn't have to be absolutely exact, so what you've put together is perfect.

 

[Vogateer]

Thanks Vogateer! That worked - did take a while to run though! Changed the highlighted above to match the dataset. Thanks so much for your help!
It doesn't have to be absolutely exact, so what you've put together is perfect.

Glad to hear it's working! I wish I were clever enough to find a way to calculate this more efficiently, but if you don't need to be accurate down to the second, you could say that a 2 or 3 second overlap is needed before counting the calls as simultaneous, you could change the last 60 to a 30 or 20 and reduce how many specific times are being checked by half or more:

=MAX(LET(start,$A2,end,$B2, all_start, $A$2:$A$1000, all_end, $B$2:$B$1000,time_seq,SEQUENCE((end-start)*24*60*30,1,start,1/(24*60*30)),COUNTIFS(all_start,"<="&time_seq,all_end,">="&time_seq)))