Concurrent Calls

[Andrew]

I have a worksheet which contains the time a call came in and the time it
ended. What I am trying to find out is for each one hour period during a
day (or even better, what 30 minute period), what was the peak (maximum)
number of callers at any one time and what time (if possible, in 5 second
increments), were there the most concurrent callers.

To set this up, I have one spreadsheet with the values of the date(38109 =
05/02/04)

formatted date date
05/02/04 12:00:35 PM 38109.500405083700
05/02/04 12:00:40 PM 38109.500462954100
05/02/04 12:00:45 PM 38109.500520824500
05/02/04 12:00:50 PM 38109.500578694800
etc. in 5 second increments

On the other worksheet I have the call records:
call_start call_end
38109.50041 38109.5024
38109.50431 38109.5048
38109.5055 38109.5125
....etc

So for this example, the peak number of callers would be 2 at 12:00:35 PM
(38109.500405..)

Any strategies you can pass along would be of great help. Is this something
that I can only solve using ExcelVBA or is there an array formula that can
handle this?

Sorry for the cross-post, but just want to cover my bases with finding a
solution.

-Andrew

 

[Frank Kabel]

Hi
try the following:
Assumptions:
- sheet 1, col. a contains your date values starting in row 1
- sheet 2, col A contains the call start and col. B the call end.

Enter the following formula in cell B1 on sheet 1:
=SUMPRODUCT(--('sheet2'!$A$1:$A$1000>=A1),--('sheet2!$A$1:$A$1000<A2))
this counts all calls STARTED in your 5 second range. Copy this formula
down for all rows

 

[Andrew]

Frank, thanks for the quick response and the interesting formula.

This is close but it doesn't quite get me to where I need to be. This tells
me that I received x number of calls during that 5 second period but doesn't
take into account the other calls that are already in the system. So I
might have 40 lines in use, but this function returns me a result of 5.

Hope this clarifies it.

As an aside, what does "--" do in both parts of this formula?
-Andrew


Hi
try the following:
Assumptions:
- sheet 1, col. a contains your date values starting in row 1
- sheet 2, col A contains the call start and col. B the call end.

Enter the following formula in cell B1 on sheet 1:
=SUMPRODUCT(--('sheet2'!$A$1:$A$1000>=A1),--('sheet2'!$A$1:$A$1000<A2))
this counts all calls STARTED in your 5 second range. Copy this formula
down for all rows

 

[Frank Kabel]

Hi
than try
=SUMPRODUCT(--('sheet2'!$A$1:$A$1000>=A1),--('sheet2!$A$1:$A$1000<A2))
+ SUMPRODUCT(--('sheet2'!$A$1:$A$1000<A1),--('sheet2!$B$1:$B$1000>=A1))


the '--' coerces the boolean values to numbers (TRUE=1/FALSE=0)

 

[Andrew]

Works great and I was able to verify a couple of periods.

Thanks!
Andrew


Hi
than try
=SUMPRODUCT(--('sheet2'!$A$1:$A$1000>=A1),--('sheet2!$A$1:$A$1000<A2))
+ SUMPRODUCT(--('sheet2'!$A$1:$A$1000<A1),--('sheet2!$B$1:$B$1000>=A1))


the '--' coerces the boolean values to numbers (TRUE=1/FALSE=0)