Link to home
Start Free TrialLog in
Avatar of colonialiu20
colonialiu20Flag for United States of America

asked on

Use excel to count number of concurrent external calls

I am contemplating switching my company phone service from a traditional pri circuit to a virtual sip trunk.  I need to determine the maximum number of calls at any one time.  I have a spreadsheet (see attached) that contains start_timestamp, answer_timestamp, end_timestamp, destination, billsec, duration, and direction.  The time stamps are down to the second.  I'd like to calculate how many simultaneous calls are happening at any given time so I can order the correct amount of telephone lines from the phone company.  To complicate things, i do not need to count calls that are between internal 4 digit extensions.  For example, i do not need to count a call between 3876 and 3954.  I only need to count a call if either the caller or destination is an external number.  Ive googled around but cant find anything that does specifically this. Thank you!!!
cdr.xls
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

You wrote:

To complicate things, i do not need to count calls that are between internal 4 digit extensions.  For example, i do not need to count a call between 3876 and 3954.  I only need to count a call if either the caller or destination is an external number.

I am looking at your sample file now, and it is far from clear what calls "internal".  Can you clarify that, please?

:)
Assuming that you mean to count only those calls with entries in Columns D and/or E...

1) In another area on that worksheet (or even on a separate worksheet), make a column of evenly spaced date/time values.  I spaced mine 20 seconds apart, but 10 or 15 seconds would do as well.  For example:

Time
3/22/13 8:00:00 AM
3/22/13 8:00:20 AM
3/22/13 8:00:40 AM
3/22/13 8:01:00 AM
3/22/13 8:01:20 AM
3/22/13 8:01:40 AM
3/22/13 8:02:00 AM

et cetera

2) Use a formula to count the number of calls active at each time:

=SUMPRODUCT(($A$2:$A$3566<=K2)*($C$2:$C$3566>=K2)*(LEN($D$2:$D$3566&$E$2:$E$3566)>0))

3) The max of that column is an estimate of the maximum number of concurrent calls.  The shorter you make the interval, the better the estimate gets

Please see this file for an example:

Q-28074368.xls
Avatar of colonialiu20

ASKER

I am reviewing my original attachment.  I must have inadvertently messed up the internal calls while i was masking the phone #s.  Hold on
Here is an update file.  Internal calls are calls between two 4 digit extensions.
cdr1.xls
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial