Solved

need help finding out number of concurrent calls within excel

Posted on 2014-10-08
6
552 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 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
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 

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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

Suggested Solutions

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

856 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