Solved

need help finding out number of concurrent calls within excel

Posted on 2014-10-08
6
667 Views
Last Modified: 2014-10-09
Hi Experts, I am needing to find out my maximum number of concurrent calls at any time of the day, I think I am close with the attached sample but I need help getting the sumproduct formula to calculate how many active calls there were for that particular second of the day.  A sample below is for 09/30/14, I am thinking once I have this formula I can do a new sheet per day and populate the start time and end time then it will let me know the most concurrent calls I have had within the entire month.
I need the proper formula in the concurrent call column so I can copy it down the sheet and populate all seconds of the day.

Thanks so much in advance for any assistance you can provide!
Call093014-WORKSHEET.xlsx
0
Comment
Question by:mmoniz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
ID: 40368978
i am not sure if i understood question correctly.  but is it something like the attached one that you are looking for ?

see attached
Monthly-Overview.xlsx
0
 

Author Comment

by:mmoniz
ID: 40369048
looks like that may work, I tried applying that formula to my sheet but it does not show any calls calculating.  attached is what I did trying to match your sheets formula to what I have populated.

the end result I need to get too is for every second of the day(column H) how many active concurrent calls there were based on the start time (column A) and the end time(column B or C)

I attached my attempt to apply your COUNTIF formula in case you can see what I am missing to find the result I need.

Thanks again,
Mike
Call093014-WS-Countif.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 250 total points
ID: 40369544
Mike,

Part of the issue is that the data types in columns A & B are not actual internal time values and are different from the timevalues in column H.  Converting them as such should resolve it.  Nothing is wrong with the formula; I checked some of the concurrent results (especially the max values) and when filtering on A & B to same time criteria, the answer is correct.

See the attached workbook.

-Glenn
EE-Call093014-WS-Countif.xlsx
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:mmoniz
ID: 40370001
Awesome, Thanks so much for your help guys, that is exactly what I needed!  Much appreciated!
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40370502
Thank you  Glenn,

I used function of =TimeValue  to convert the COlumn A and B with the correct time values and then it worked. is that what you did exactly?

or you had a better way to do it, as usual
0
 

Author Comment

by:mmoniz
ID: 40371106
IT is strange when I try to do the same it does not work, I highlight both columns A and B and I right click and select Format Cells then I select Time and the same option you have in the sheet you sent 13:30:55, it still does not work and change it to a time value with AM or PM showing in the cell line.  I see the AM and PM designation in the sheet you uploaded but cant seem to get my values to convert in columns A and B, am I missing something?

Thanks again for your help!
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question