Function needed to calculate arrival patterns
I have a long list of timestamps for incoming calls and am trying to organize
them into an arrival pattern chart. I need to count how many calls came in for every 1/2 hour block throughout the day and then use this data in a bar chart. Is there a function that can assist me with this? |
Function needed to calculate arrival patterns
How many days of information do you have? If it is for 1 month, then
you will have 30 (or 31) times 48 cells of derived data. Do you want this organised as a table of 48 cells across by 30 down, or as a list of nearly 1500 cells in one column, or are you only interested in the arrivals by half hour blocks for the consolidated month? I assume you have date and time data for each call - are these in separate columns or combined into one column? You should be able to use the COUNTIF( ) function, but details of how to use it will vary depending on the exact nature of your data and how you want the output to appear. Hope this helps - post back with further details if you require further help. Pete |
Function needed to calculate arrival patterns
Currently, I only have about 2 months of data (I believe about ~1200 cells),
but I'd like to keep a running count for a 12 month period. The date and time stamps are separate, so I currently put all timestamps on one worksheet in one column sorted by time. I was originally thinking of having another column with 1/2 hour increments and then a formula next to that 30 minute block that went through the entire column of timestamps, counted how many occurred within those 30 minutes and then give me a number. Those numbers could then be used in a chart. I might be totally off or complicating things too much. Please let me know if you have any suggestions. End result=chart showing number of calls per given 30 minute block of time. Thanks! "Pete" wrote: How many days of information do you have? If it is for 1 month, then you will have 30 (or 31) times 48 cells of derived data. Do you want this organised as a table of 48 cells across by 30 down, or as a list of nearly 1500 cells in one column, or are you only interested in the arrivals by half hour blocks for the consolidated month? I assume you have date and time data for each call - are these in separate columns or combined into one column? You should be able to use the COUNTIF( ) function, but details of how to use it will vary depending on the exact nature of your data and how you want the output to appear. Hope this helps - post back with further details if you require further help. Pete |
Function needed to calculate arrival patterns
It's a bit late here in the UK, so I'll just give a few comments before
I go to bed. I was just pointing out that you could have a massive amount of data, just for one month. If you arranged it as a table so that you have dates going down and the 1/2 hour increments across, you could then plot the 48 bins by a particular day (maybe on a weekly graph). You could consolidate the daily totals how you wished, ie. into weekly totals, monthly totals etc, or you could add all the Mondays' calls together, all Tuesdays' etc. You could compare one week (or month) with the previous period. With one long list of bins this becomes more difficult, though the formula to get the data into the appropriate bin is simpler. You might also want to know the number of simultaneous calls at any instant in time and plot that. So many possiblities! Just a few comments ... Goodnight Pete |
All times are GMT +1. The time now is 11:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com