1. ## 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?

Wassim

2. 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. 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

4. I was imagining something like the attached...

Steve

5. 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

#### Posting Permissions

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