Solved

need help finding out number of concurrent calls within excel

Posted on 2014-10-08
6
522 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

831 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