Avatar of colonialiu20
colonialiu20
Flag 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
Microsoft ExcelSpreadsheets

Avatar of undefined
Last Comment
Patrick Matthews

8/22/2022 - Mon
Patrick Matthews

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?

:)
Patrick Matthews

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
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
colonialiu20

ASKER
Here is an update file.  Internal calls are calls between two 4 digit extensions.
cdr1.xls
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question