Home About Kurtosis Courses Course Calendar Booking Information Ideas Technique Clients Contact | ||||

Rows of dots:
How to use Excel to visualize the asymmetry between hospital arrival and departure times After last week's posting, a few people said they thought charts like this might be a useful way of describing inflow and outflow in hospitals:
And some people said they wanted instructions on how to do it in Microsoft Excel. So here are the step-by-step instructions:
As I mentioned in last week’s article, the chart type in Excel is an X-Y chart. The x-values for the chart are
the transaction times. What we mean by "transaction times" are basically
The y-values are altogether less interesting. We are just going to assign a value of 2 to the red admissions and a value of 1 to the blue discharges. This is simply to ensure that the red dots will show higher on the chart than the blue dots (because 2 is a bigger number than 1).
In this example, we’ve picked
8th January 2010 as the day we want to look at. So we need to design and
run a query that will get us all of the AMU admission times on that day It may be simpler to think of
these as two separate queries. The Excel example here is based on two
separate queries, and that is what we have pasted into
It’ll be easier to see what’s
going on if we
You now need to get the combined list of dates (there should be 45 of them for this particular day: 23 admissions and 22 discharges) into date/time order. [Strictly speaking, you don’t actually need the data to be in chronological order for this chart to work (since we aren’t joining the dots together with lines), but it will make it easier to see what’s happening if you do do this.]
The X-Y chart will be easier to draw if the admission_times are in a separate column from the discharge_times.
You’re in The x-values: C4:C49. The y-values for the red dots: E4:E49. The y-values for the blue dots: F4:F49. In its raw, unformatted state,
the graph will look like the one in
The most important formatting step you need to take is to ensure that the x-axis measures time from exactly midnight (40186 in Excel's internal date format) to midnight (40187 in Excel's internal date format), and that you have vertical gridlines appearing at sensible intervals (e.g. every three or six hours). The chart in [20 January 2014] |
||||

© Kurtosis 2014. All Rights Reserved. | ||||