ExcelBanter - # Events over time?

"Jon M" wrote:
Is there anyone who can help me with this?

It sounds like you are asking for help with design, not simply a how-to
question about this or that Excel feature. In that case, it is much easier
to do one-on-one with some back-and-forth email than postings in a
newsgroup. If you are interested, I could offer some rudimentary help if
you write to joeu2004 "at" hotmail.com. Be sure the email subject reflects
the topic of this thread; otherwise I will assume it is spam and ignore it
without looking.

The following outlines one approach.

I have a file that contains a list of patients and arrival times and
want to see where my gaps in coverage exist, so I am looking for a wayt to
generate a plot that says the average wait (I have this time interval for
each patient) from 0100 to 0200 was x minutes, 0200-0300 was y minutes

It sounds like your file contains patients, arrival times and wait times.
Assuming you have a text file, suppose you import those fields into A1:A100,
B1:B100 and C1:C100 respectively on a worksheet renamed Data. Then on
another worksheet, populate A1:A25 with the one-hour intervals, perhaps
0000, 0100, etc, through 2400. In B1, put the following formula and copy
down through B24 (not B25):

=sumproduct((A1<=Data!$B$1:$B$100)*(Data!$B$1:$B$1 00<A2))

That computes the number of patients for each interval. Then put the
following formula into C1 and copy down through C24:

= sumproduct((A1<=Data!$B$1:$B$100)*(Data!$B$1:$B$10 0<A2),Data!$C$1:$C$100)
/ B1

That computes the average wait time for each interval. Then you can use the
Chart Wizard to create a line or bar graph.

You can also compute the total average wait time with the following formula:

=sumproduct(B1:B24,C1:C24) / sum(B1:B24)

Note that you should not simply compute AVERAGE(C1:C24) because it is
usually mathematically incorrect.

I did this once before with 4 hour intervals and it involved defining
variables that identified a particular 4 hour interval, etc., but it was a
bit convoluted and I was hoping for a better way.

The advantage of using a named ranges for Data!B1:B100 and Data!C1:C100 is
that it is easier to write and maintain formulas if the amount of patient
data changes in the future.

I cannot promise a better way since I do not know your tolerance for tedium.

----- original message -----

"Jon M" wrote in message
...
Looking for an easier way... Would like to plot # events vs time. For this
problem, I have a file that contains a list of patients and arrival times
and
want to see where my gaps in coverage exist, so I am looking for a wayt to
generate a plot that says the average wait (I have this time interval for
each patient) from 0100 to 0200 was x minutes, 0200-0300 was y minutes,
etc.

I did this once before with 4 hour intervals and it involved defining
variables that identified a particular 4 hour interval, etc., but it was a
bit convoluted and I was hoping for a better way.

Is there anyone who can help me with this?
Thanks in advance!!!!