figure out the maximum amount of concurrent calls

**nicoleemily** · 12-04-2012, 02:44 AM

Hi, I have a most similar question that I cannot seem to work with formula. I have a list of just over 100 call log entries. I need to calculate how many calls have overlapped. Times are recorded in military time. Each list is based on a 24 hour period.

Columns are:
E. Start time
Q. End time
R. Duration

Please note I have read the earlier threads on this topic and those are customized for a different set of rules.

When I say overlapped, I mean:
If any rows' start, elapsed/duration or end time is equal to any other rows start, elapsed/duration or end time.

Any help is greatly appreciated.

**NickyC** · 12-04-2012, 04:18 AM

Hi
assuming your start data are in e2:e101 and end data are in Q2:q101, try this formula

Please Login or Register  to view this content.

for each row, it will return the number of other rows that overlap with that row's time period

**nicoleemily** · 12-04-2012, 02:15 PM

Hi NickyC,

I tried the formula, it appears to be calculating. I was wondering if there's a way to have it calculate the maximum amount. I have attached a worksheet for you. Let's say it returns 2, 3 and 4 calls. I need the formula to evaluate that the maximum was 4, for example.

**ChemistB** · 12-04-2012, 02:22 PM

You already entered the formula =MAX(S2:S132) How does that differ from your question?

**nicoleemily** · 12-04-2012, 02:33 PM

Hi ChemistB,

Apologies about that. I wasn't sure some of my formulas were true. Is there a way to check whether the results are true?

**nicoleemily** · 12-04-2012, 02:40 PM

The formula NickyC gave me tells me how many have overlapped. I need to figure out how many overlapped at any given time. I hope that makes sense

**ChemistB** · 12-04-2012, 03:07 PM

The number in column S tells you how many calls overlap with the call in that row. This is a time range (start to finish)
If you want to know how many calls are ongoing at the same time as a specific time (i.e. the start time)
=COUNTIFS($Q$2:$Q$101,">="&E2,$E$2:$E$101,"<="&E2)
For end time
=COUNTIFS($Q$2:$Q$101,">="&Q2,$E$2:$E$101,"<="&Q2)
Does that help?

**nicoleemily** · 12-04-2012, 06:16 PM

Hi ChemistB,
I am testing it out now. Is there a way to have it return which cells it is getting the information from?

**ChemistB** · 12-05-2012, 09:41 AM

Here's a way to test it. I copied a value for start and end times to U1 and V1. In my attached example, I did this from row 34

I selected the range E2:S132 and went to conditional formatting>New rule>Use formula

=OR(AND($E2>=$U$1,$E2<=$V$1),AND($Q2>=$U$1,$Q2<=$V$1)) formatted as green fill "OK"
Any row that overlaps row 34 will turn green.
Move new values to U1 and V1 to test other rows.
I did notice your formula in Col S was off.
You need your ranges to match your data
=COUNTIFS($Q$2:$Q$132,">="&E2,$E$2:$E$132,"<="&Q2) (it was only to 101 instead of 132)
If this data size is constantly changing, you might want to look into dynamic named ranges
http://www.contextures.com/xlNames01.html#Dynamic

**nicoleemily** · 12-05-2012, 01:13 PM

Hello Chemist, I am testing your formula out now. BTW, both your and Nick's feedback was extremely helpful!

**nicoleemily** · 12-05-2012, 01:46 PM

Hi chemist,
The formula in the updated worksheet, is that the syntax for the main formula? You noted earlier there were (2), one that checks start time and one that checks end time.

**ChemistB** · 12-05-2012, 02:12 PM

The latest spreadsheet I uploaded uses Nicky's formula to come up with the overlaps. If you want to know how many calls are going on at any particular point in time, best to enter this time (including date) in a separate cell and use a formula to count how many calls are ongoing at that moment.

Something to think about,
Let's say you're looking at a call from 9:25 AM to 9:37 AM
A call from 9:22 to 9:40 would intersept both beginning and end times
A call from 9:26 to 9:32 would intercept neither (but would count in Nicky's formula)
It's important to figure out exactly what you want to count.

Tell me exactly what you want (a time period, beginning time, end time, a specific timepoint (not necessiarly beginning or ending) or some combination of these) and I'll tell you what formula(s) to use.

**nicoleemily** · 12-05-2012, 02:33 PM

Hi,
My goal is to figure out the maximum amount of simultaneous calls at any given time. Basically, if there are 5 people on the phones at 9:38am for example, I need "5" as a result; if there are 3 on the phones at 1:21pm, for example, I need it to return 3. After the calculations are done, then I need to put the highest number, like the maximum amount of people on the phones at any given time. We are trying to figure out how many trunks we need to purchase and must avoid anyone getting a busy signal. If the maximum is 4, for example, we will purchase 4, in turn will allow a maximum of 4 people on the phones concurrently without an inbound call getting a busy signal.

Also, your latest worksheet, I am having trouble finding the formula for it turning a different color. I have attached a months view of the calls I am trying to figure out. Hopefully you may plug that formula in at the top and I can do the rest.

Thank you for all of your wonderful help and expertise!

**ChemistB** · 12-05-2012, 03:37 PM

Okay, I think the best way to do this is have a column which goes sequentially by minutes and counts how many calls are ongoing any particular minute. I set this up in Column X. You'd just need to enter the first value in X2 and the rest will autopopulate. Then in Y, it counts how many people were on the phone at exactly that time. This gives you much lower numbers than the count over a time period which you have in Col U. The max of 15 for that one was on a call that lasted nearly an hour.
Hope this helps.

**nicoleemily** · 12-05-2012, 04:55 PM

AWESOME! Excellent ingenuity! Thank you. It is exactly what I've been looking for! If you have any questions about perl or PHP don't hesitate to ask me.

**ChemistB** · 12-05-2012, 05:27 PM

Thanks nicoleemily. Glad I could help.

**nicoleemily** · 12-05-2012, 05:32 PM

Sorry Chemist, last Q, would it be too complicated to see which cells it refers to when it counts out the number? The same type of formula you did with making it highlight the cells green?

**ChemistB** · 12-05-2012, 06:02 PM

I did that on columns X and Y. I can modify to turn the other columns green too.
To find the values that match the max in Y, Select Y and hit CNTRL F (Find)
Select options and make sure it reads "Look in Values" and then do "Find Next" to move down the page.
I will work on this tomorrow.

**nicoleemily** · 12-05-2012, 06:06 PM

Yes, like which of the cells in column start time and end time match the minute by minutes

**ChemistB** · 12-06-2012, 10:31 AM

Okay, Here's what I did. I used formulas to pull up all the times in Col V that match the maximum calls. Then I used conditional formatting to color cells that fall in those times. Now some of the times are very close together (like 9/24/12 2:13:00 PM and 9/24/12 2:14:00 PM) but the cell can't show two colors so it picks the first one that it gets to.

Instead of scrolling down to find the overlaps, you can use Excel's sorting. Select one of the columns (A:T)and choose Sort by Color>Custom Sort
Then "Sort on Cell Color" and "No Cell Color On Bottom"
Better?

Note: I had to zip this file, it's getting bigger.

**nicoleemily** · 12-06-2012, 05:03 PM

Originally Posted by ChemistB

Okay, Here's what I did. I used formulas to pull up all the times in Col V that match the maximum calls. Then I used conditional formatting to color cells that fall in those times. Now some of the times are very close together (like 9/24/12 2:13:00 PM and 9/24/12 2:14:00 PM) but the cell can't show two colors so it picks the first one that it gets to.

Instead of scrolling down to find the overlaps, you can use Excel's sorting. Select one of the columns (A:T)and choose Sort by Color>Custom Sort
Then "Sort on Cell Color" and "No Cell Color On Bottom"
Better?

Note: I had to zip this file, it's getting bigger.

How do I replicate the dropdowns over to the new months (oct and nov)?

**ChemistB** · 12-06-2012, 05:41 PM

When you paste in the new data, then
CNTRL SHFT 8 to select the range, then Home> Sort & Filter> Filter
That should do it.

**nicoleemily** · 12-06-2012, 05:55 PM

Hi Chemist,

the field called 'overlapped' I didn't realize we would still use it since the minute by minute and count columns seems to have resolved my questions. Am I wrong?

**nicoleemily** · 12-06-2012, 06:04 PM

does the max refer the times below or refer to it as one of the sets of 4?

12537521860 9/10/12 10:40:32 AM 648 0.132 10:51:20 AM
12537521860 9/10/12 10:46:54 AM 172 0.036 10:49:46 AM
12065272689 9/10/12 10:47:33 AM 106 0.024 10:49:19 AM
12065272689 9/10/12 10:48:38 AM 107 0.024 10:50:25 AM
12065272689 9/10/12 10:49:39 AM 58 0.012 10:50:37 AM

**nicoleemily** · 12-06-2012, 06:06 PM

I think ive got it now, takes careful studying to understand. very good work!

**ChemistB** · 12-07-2012, 09:41 AM

Hi Nicoleemily,
Yes, you are correct, you no longer need the overlapped column and yes, those times all tie for the max number of simultaneous calls.

**nicoleemily** · 12-08-2012, 07:19 PM

Thank you for all of your awesome help ChemistB!!!

figure out the maximum amount of concurrent calls

LinkBack

Thread Tools

Rate This Thread

Display

figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Re: figure out the maximum amount of concurrent calls

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions