Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
# Events over time?
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!!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
# Events over time?
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" wrote: 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!!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
# 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!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Graph of events in time | Charts and Charting in Excel | |||
Average time spent to # of events | Excel Worksheet Functions | |||
Need formula for time between two events | Excel Worksheet Functions | |||
Chart for events per unit of time??? | Charts and Charting in Excel | |||
How can I calculate events that happened at the same time? | Excel Discussion (Misc queries) |