Solved

need help finding out number of concurrent calls within excel

Posted on 2014-10-08
6
432 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
  • 3
  • 2
6 Comments
 
LVL 25

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

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

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Photo Albums in PowerPoint Photo Albums are a very useful tool in PowerPoint and allow you quickly add a large number of images. The images can be formatted in a variety of ways so that you are able to create a professional looking presentation v…
Outline Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now