Stair Steps to Heaven
How to draw stair step control limits on a control chart in Microsoft Excel
One of the things we discover as we learn more about SPC in general (and control charts in particular) is that control limits on charts aren’t always horizontal and straight. Sometimes they are uneven and variable.
We can draw variable-control-limit control charts in Excel using the usual line chart functionality. Here's one:
But we always end up with a pale imitation of the control charts you see in the textbooks that are always drawn with stair-stepped control limits.
What we really want is a stair-stepped control limits:
This article shows you how to draw a "proper" stair-stepped control chart.
Let’s start with a dataset that is crying out for variable control limits.
We’re going to look at twelve hours in the life of a Minor Injuries Unit.
The thing that we’re measuring is the average time spent by patients in the Unit, broken down by hour of arrival.
The basic dataset looks like the worksheet Summary Table in the Excel 2003 workbook called Stairsteps.xls that you can download here.
You can see that to draw the X-Bar chart we’ve used standard error to calculate the width of the upper and lower control limits. The limits are wide when there were just a few attendances (for example, between 8am and 9am, when there were just five attendances); the limits are narrower in the busier hours (for example, between 2pm and 3pm, when there were 21 attendances).
If we want to make the control limits so that they are stair-stepped instead of merely diagonal, you need to follow the following steps:
Insert an extra row into the worksheet straight after the last hour. Label this row (in cell A19) 21:00.
Insert two columns to the right of the Lower control limit column. You will now have two blank columns (H and I). Label column H: LCL X error bar. Label column I: LCL Y error bar.
In cells H6:H18 type the value 1. The pre-formatting will make it so that it appears as 1.0. This will allow the chart (when you get round to drawing it) to draw a horizontal line one unit to the right of the values in column G.
In cell I7 type the formula =G7-G6. This should give you a value of 5.9.
Copy this formula down (without any anchoring) to cell I19.
This will allow the chart to draw a vertical line 5.9 minutes upwards from the horizontal line you enabled in the previous paragraph.
If you don’t understand any of this, don’t worry, it will (hopefully) become clearer as we move through the exercise.
Create two extra columns in columns K and L. Label them UCL X error bar and UCL Y error bar.
Type the value 1 into cells K6:K18.
Type the formula =J7-J6 in cell L7. You should get the value -5.9. Copy this formula down to L19 (without any anchoring).
You now need to insert a blank column immediately to the right of column A. Label it New Arrival Hour and type in values that are 0.5 greater than the original values in column A. So in cell B6 you will type 8.5, in cell B7 you will type 9.5, and so on, all the way down to 20.5 in cell B18. [You don't need to be Einstein to realise that there's a formula that will do this for you.]
You are now ready to plot the chart. Which you are going to do by drawing a scatterplot. And which you are going to do whilst omitting the data that supplies the jagged line. You are only going to plot the horizontal centre line (F6:F19), and the lower and upper control limits (H6:H19) and K6:K19).
In addition to these three series, you will also need to plot the hour of day (A6:A19)
So you should select the following ranges before going into Excel’s chart wizard:
Go into the chart wizard, choose X-Y chart and select the subtype that Excel describes as Scatter with data points connected by smoothed lines without markers.
You should end up (once you’ve got rid of the grey background and the gridlines) with a chart that looks something like this:
You now need to format this chart as follows:
The middle horizontal line needs to be black and solid The yellow and magenta lines need to be black and dotted The x-axis scale goes from 08:00 to 21:00 in units of one hour.
It should now look like the chart below:
Now for the stair steps
We’ll do the upper control limit stair steps first.
Right-click on the upper broken line and choose Format data series…
Click on the X Error Bars tab. Click on Custom: and position your cursor in the + field. Select the cells L6:L18 from the Summary Table worksheet. Then click on the Y Error Bars tab, click on Custom: and position your cursor in the – field.
At this stage your chart should now look like this:
You now need to follow the same sequence of steps to create the stair steps for the lower control limits:
Once you’ve done this you can then format the error bars so that they have no horizontal line markers and so that the line style is a broken line.
Then you can format the wavy line so that there is no line visible (but whatever you do, don’t delete the line altogether, because that way you’ll lose everything!).
And you should end up with a chart that looks like this:
You’ve now got everything you need apart from the jagged data line.
In order to have a jagged line with blue dots positioned in the middle of each of the stair step control limits, what you have to do is superimpose this chart (the one immediately above, the one labelled Step (11) in the Excel workbook) over the top of a chart (which you are about to create) that contains only the blue jagged dotted line.
So you just need to plot a simple line chart that shows just the missing data. Select cells B6:B19 and D6:D19. Don’t worry about the fact that there is no data in row 19, just do it anyway.
Ensure that the x-axis labels are assigned to the range B6:B19 and you should get a chart that looks like this:
And now, to finish the job, you just have to copy the previous chart – the one with the horizontal black line and the dotted stepped control limits – and paste it over the top of this chart.
You will end up with a chart that looks like this:
[10 May 2012]
|© Kurtosis 2012. All Rights Reserved.|