Simultaneous calls

[farid2001]

Dear Gentlemen

I need a formula that will let me determine the maximum amount of
simultaneous calls in a 24 hour period, what I have is:

Call started at Duration
03/03/2009 09:44:57 00:00:40
03/03/2009 09:38:32 00:00:22
03/03/2009 09:37:13 00:02:46
03/03/2009 09:35:39 00:01:28
03/03/2009 09:35:32 00:00:49
03/03/2009 09:34:31 00:00:17
03/03/2009 09:33:26 00:00:51
03/03/2009 09:31:48 00:00:25
03/03/2009 09:31:01 00:01:29
03/03/2009 09:27:29 00:00:49
03/03/2009 09:26:29 00:00:31
03/03/2009 09:24:00 00:15:04

In this case the maximum of simultaneous calls is 3, but I need a formula
that will give me the answer.

I'm kind of new to excel, so help will be greatly appreciated.

Thanks & regards
farid2001

 

[Shane Devenshire]

Hi,

What does "maximum" simultanious mean? Either a call is simultanious or it
isn't.

Also, what if three calls overlap, do they count as 2 or 3 or 1? In your
sample the last (first) call is 15 minutes and overlaps all the rest of the
calls, so I'm not sure how you come to a result of 3?

 

[farid2001]

Hello Shane

Simultaneous calls means how many calls are taking place at the same time,
the first call lasts 15 mnts, and it counts as 1 call, during this 15 mnt
call, in 2 ocations we have 3 calls running at the same time, I've added
another column adding start time + duration:

Date Duration call ends at
03/03/2009 09:44:57 00:00:40 09:45:37
03/03/2009 09:38:32 00:00:22 09:38:54
03/03/2009 09:37:13 00:02:46 09:39:59
03/03/2009 09:35:39 00:01:28 09:37:07
03/03/2009 09:35:32 00:00:49 09:36:21
03/03/2009 09:34:31 00:00:17 09:34:48
03/03/2009 09:33:26 00:00:51 09:34:17
03/03/2009 09:31:48 00:00:25 09:32:13
03/03/2009 09:31:01 00:01:29 09:32:30
03/03/2009 09:27:29 00:00:49 09:28:18
03/03/2009 09:26:29 00:00:31 09:27:00
03/03/2009 09:24:00 00:16:04 09:40:04

So I arrive to 3 simultaneous calls since while first call was taking place,
call 4 & 5 were also taking place, same goes for calls #'s 10 & 11

Is there a formula that could determine how many calls are taking place at
the same time?

Thanks & regards
farid2001

 

[Shane Devenshire]

Hi,

I've been working the past few days so this is the first chance I had to
revisit this problem. You need a custom VBA Function to do what you want.
Try this one:

Function SMax(rStart As Range) As Integer
Dim dFirst As Double
Dim cell As Range
Dim dLast As Double
Dim myLast As Double
Dim myCount As Integer
Dim T As Double
Dim myMax As Integer

dFirst = WorksheetFunction.Min(rStart)
For Each cell In rStart
myLast = cell + cell.Offset(0, 1)
If myLast > dLast Then
dLast = myLast
End If
Next cell
For T = dFirst To dLast Step 0.000011574
myCount = 0
For Each cell In rStart
If T >= cell And T <= cell + cell.Offset(0, 1) Then
myCount = myCount + 1
End If
Next cell
If myCount > myMax Then
myMax = myCount
End If
Next T
SMax = myMax
End Function

 

[farid2001]

Hello Shane

Thank you very much, your code worked very well.
I had to change the time interval from 0.000011574 (1 second) to 0.000694444
(60 seconds), since it took too long to process a range of 900 cels.

Is there a way to speed up the code, I did add application.ScreenUpdating =
False to your code, but still takes about 1.5 minutes to process the answer.

I really did appreciate your help.

Thanks & regards
farid2001