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

Rows of dots: step by step

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:

NHS ANYWHERE | ANYTOWN GENERAL HOSPITAL

23 admissions toand 22 discharges fromthe Acute Medical Unit (AMU) : Friday 8th January 2010

And some people said they wanted instructions on how to do it in Microsoft Excel. So here are the step-by-step instructions:

Preamble

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 both the arrival and the departure times. We need arrival times for the red dots and we need departure times for the blue dots.

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

Step One

Retrieve the times of admission and times of discharge that you want to visualize

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 and all of the AMU discharge 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 Sheet (1) of this Excel workbook:

Rows of Dots Step by Step.xls

Step Two

Merge the admission times and discharge times into one list

It’ll be easier to see what’s going on if we merge these two lists (the admission times and the discharge times) into one list. But before we do that, we’ll need to remember to label them (in column D) as either admissions or discharges,.

Sheet (2) shows this step completed.

Step 3

Sort the merged list data into chronological order

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

Sheet (3) shows this step completed.

Step 4

Place the transaction_type indicators in different columns

The X-Y chart will be easier to draw if the admission_times are in a separate column from the discharge_times.

Sheet (4) shows how a relatively straightforward =IF statement can achieve this. The NA() bit of the statement is simply there to prevent Excel from trying to plot zero values on the chart.

Step 5

Draw the X-Y chart

You’re in Sheet (4) and you need to select three data series:

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 Sheet (5).

Step 6

Formatting the chart

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 Sheet (6) shows how I’ve done it (with all of my minimalist chart design preferences on show!)

[20 January 2014]


 

Make a comment on this blogpost:

Name:

Job Title:

Organisation:

Email address:

 

     
© Kurtosis 2014. All Rights Reserved.