Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 23rd 06, 05:10 PM posted to microsoft.public.excel.worksheet.functions
klwinston
 
Posts: n/a
Default 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?

  #2   Report Post  
Old January 23rd 06, 08:36 PM posted to microsoft.public.excel.worksheet.functions
Pete
 
Posts: n/a
Default 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

  #3   Report Post  
Old January 24th 06, 01:04 AM posted to microsoft.public.excel.worksheet.functions
klwinston
 
Posts: n/a
Default 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


  #4   Report Post  
Old January 24th 06, 01:18 AM posted to microsoft.public.excel.worksheet.functions
Pete
 
Posts: n/a
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Function Help Needed Harvey Gersin Excel Worksheet Functions 4 November 29th 05 11:01 PM
help needed in VLOOKUP function DaredEvil590 Excel Worksheet Functions 2 November 25th 05 05:06 PM
Desperate Help needed with a function. Lori Excel Worksheet Functions 3 July 21st 05 03:19 AM
How do I use the IF function to calculate date Pulling My Hair Out! Excel Discussion (Misc queries) 1 December 10th 04 11:03 PM
What function or formula do I use to calculate ROI with this data? Dave Excel Worksheet Functions 9 November 5th 04 02:42 PM


All times are GMT +1. The time now is 01:32 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017