# Events over time?

  • Thread starter Thread starter Jon M
  • Start date Start date
J

Jon M

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!!!!
 
these type problem usally you have to to it by time period. Most people will
list in column a 15 minute time periods from midnight to midgnight. And then
in column B put the average wait time for ea h time period.

time average wait
12:00 AM
12:15 AM
12:30 AM
.....
11:45 PM
 
these type problem usally you have to to it by time period. Most people will
list in column a 15 minute time periods from midnight to midgnight. And then
in column B put the average wait time for ea h time period.

time average wait
12:00 AM
12:15 AM
12:30 AM
.....
11:45 PM
 
Jon M said:
Is there anyone who can help me with this?
Click to expand...

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
Click to expand...

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$100<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$100<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.
Click to expand...

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 said:
Is there anyone who can help me with this?
Click to expand...

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
Click to expand...

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$100<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$100<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.
Click to expand...

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 -----
 
Back
Top