Hope someone can help!
I’m trying to do some analysis on Call Data in Excel.
I’m trying to work out the number of concurrent calls that were happening when call started. Please see same data.
Some of the calls are quite long and span over midnight so it shows as different date to confound matters.
Any suggestions?
Hello and welcome to the forum.
so you want the number of calls which started after the first call i.e., after 17:30:45 and may be ended before first call ended i.e. 00:01:27.. correct ?
Regards,
DILIPandey
<click on below * if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Hi DILIPandey
Thanks for the reply and the welcome
What I'm after is the number of simultaneous calls that were happening when a call began (if that makes sense) for example.
on row 4 there was a call that started at 10/12/2012 17:45:09, at this point there were 2 concurrent calls happening as there was a previous call (row 3) that started at 17:42:59 and finished at 17:48:27.
So the value i would need against row 4 (10/12/2012 17:45:09 00:13:17 17:58:26) would be 2. As there would be 2 simultaneous calls happening at that point
Hope this makes sense.
Try in E3 and drag down:
=IF(A3+B3<A2+B2+C2,2,"")
Quang PT
Thanks for this
This does flag calls that have more than 1 simultaneous call at a time with a 2. There are further calls that have anything up to 5 concurrent calls.
Is there a way it can show the actual number of simultaneous calls when the call started?
Sorry Quang thanks again, I've just noticed that there's some simultaneous calls that aren't showing up.
Some of the simultaneous calls won't be on the line directly above
Any other suggestions on how I can resolve this?
Could you pls re-post a new example with the real case?
try this in E2:
Formula:
Please Login or Register to view this content.
drag down
in your example it gives the following results:
1
1
2
2
1
1
1
2
1
(note: this also counts the call that is the comparison call, so a 2 means the call being checked + 1 other, etc..)
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
I hate to reopen an old thread but this one is relevant to a project I'm working on.
I'm running into an issue when the sessions/phone calls cross midnight as the sumproduct example equation only takes into account the start date to make sure the events started on the same day.
I'm thinking the best way to account for this would be to change the first argument to if CELL is between or equal to the start and end dates =1, else=0
but then b and d get complex as well since it actually runs 1600->2400->400 (for example), having trouble wrapping my head around everything.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks