Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Help with charting

    Hello All Loungers

    I need some help in getting some charts done.

    I am charting the hours and days most visits to a medical clinic happen to aid in adequate staffing of clinic.

    I would like a chart for:
    1) The hour in which most visits start
    2) The day of week the most visit happen
    3 The length of visits

    I have the data in columns:
    Col. A) Day of week. I am envisioning a bar chart where the x-axis is the days of week and the Y axis is the number of visits.

    Col. B) Time of visit, that is starting time. I think for this one, I would like to see a line chart where the x axis is the hours of operations of the clinic and the y axis is number of visitors in that hour. We can simplify this by saying if anyone come between 07:00 and 08:00 we will consider this a 07:00 hour visit.

    Col. C) End time of visit. This could be done by a Max formula, subtracting start time and end time and getting the max of the series, but I think, maybe a bar chart where we can analyse each type of visit will be more beneficial in the future.

    So how should I lay down the data?

    Thanks for your help.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I imagine entering data into 3 cols: A date, a time in, and a time out [I presume this is what is currently being collected in some form, though it probably has additional columns for other data not pertinent to these charts, names, doctor, reason for visit, etc ].

    From this you can calculate the time of visit [=Out - In]. You can also calculate [using =WEEKDAY(Date)] the day of the week that the visit was on, and the "nominal starting time" [=INT(TimeIn*24)/24].

    With these calculated columns you could create datatables for the charts.
    Chart1 could be a column chart created with a countif using a list of hours and the nominal start time
    Chart2 could be a column chart created with a countif using a list of Days of week and the weekday column
    Chart3 could be a column chart created with frequency or a countif for time of visit between 2 ranges in time

    If you want you can attach a workbook with some sample data (date, timein, timeout) I could work up some an example output to show you what I mean.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thanks Steve

    I will try your suggestions, but what I need is the setup of data.

    For example: For the Hour of most visits will the hours be in a column or row? And where will the number of entries that fir each hour be, in a column left or right of it or a row below.

    I guess I am having trouble seeing the spreadsheet aligned properly to accomplish this charting.

    Thanks for any help.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I was imagining something like the attached...

    Steve
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank You Steve

    You know some times you can't see the worksheet from the cells, LOL. For some reason one some times need to see something that is so up-front but yet so obscure.

    Your example workbook did the trick. I owe you one.

    Thank you again.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •