Understanding Your System Part 3 : Creating a Control Chart in Excel or LibreOffice calc

I covered the use of Control Charts for understanding variation, future performance and spotting signals in systems in Parts 1 and 2.  One challenge many people encounter when they begin to use these charts is how on earth to create them.  There are plenty of tools out there that are capable of producing them, some free some paid for.   In either category I have not yet found a package that gives me a combination of the control, flexibility and simplicity that works for me.

Fortunately, it is a fairly trivial exercise to create these using common Spreadsheet packages, and here is how;

The instructions below work for Excel 2010 upwards (they probably work for earlier versions but I haven’t tested) and for LibreOffice calc.

Step 1 – Assemble the data you wish to plot on your control chart and place it in two adjacent columns in a new worksheet.

Substitute <label> and <value> column headings for labels meaningful for the purpose of your chart).

The example below shows a sample size of 12, obviously you can make this larger or smaller depending on the data you wish to chart.  However, any fewer than 11 data points will significantly reduce the statistical reliability of the Control Limits.

Step 1

Step 2 – Calculate the Mean

  • Add another column heading (Col C) called “Mean”
  • Calculate the Mean value in the row below (C:2) by typing the following into the cell;

= AVERAGE(B$2:B$13)

(substituting 13 for the row number that marks the end of your sample) (The $ in front of the 2 and 13 is important because it “locks” the cell references so that your spreadsheet tool doesn’t try to be clever and update the references when we take the next step of copying the expression we just entered onto the other blank rows.)

Step 2

Now copy the formula you just entered into cell C:2 into the other cells below it, until there is a value adjacent to each of the data items in your sample;

Step 3

Step 3 – Calculate the Variation Between Each Data Point

  • Add another column heading (Col D) called “Variation”
  • Enter the following formula into cell D:2

=ABS(B2-B3)

(on this occasion, omit the $ sign in front of the row numbers as this time we want Excel to be clever and update the cell references)

Now copy the formula down to all of the remaining rows in the data set except for the last one – we want to leave this blank because the last data point has no subsequent data point upon which to calculate the variation.

Step 4

Step 4 – Calculate the Mean Variation

  • Add another column heading (Col E) called “Mean Variation”
  • As with the “Mean” Column (C), enter the formula into Cell E2 to calculate the Mean of the Variation column

= AVERAGE(D$2:D$12)

Note we are using the $ sign again to lock the row references in readiness for the next step which is to copy the formula down to all the rows up to the end of the data;

  • Copy the formula down to fill the whole data set as before;

Step 5

Step 5 – Calculate the Upper and Lower Control Limits

  • Create two new columns “LCL” in Cell F1 and “UCL” Cell G1 respectively
  • Enter the following formula into Cell F2 (Lower Control Limit)

=C2-(E2*2.66)

this formula reflects the formula we explored in Part 2;

Lower Control Limit = Mean – (Mean Variation * 2.66)

  • Enter the following formula into Cell G2 (Upper Control Limit)

=C2+(E2*2.66)

this formula reflects the formula we explored in Part 2

Upper Control Limit = Mean + (Mean Variation * 2.66)

Now copy both formulae down to fill the whole data set as for the other columns;

Step 6

Note the Lower Control Limit – in the data above, the Lower Control Limit has been calculated as -9.43758.  It is important to consider the context of the data being shown.  If this data were my journey by cycle to the office in minutes, the data above would be telling me that I am 99.7% likely to take between minus 9.43758 minutes and 137 minutes to get to work tomorrow, which makes no sense.  You are likely to have to make a judgement about whether a negative Lower Control Limit makes sense for your data or not.  If it doesn’t, you can force a negative LCL to zero by modifying the LCL formula to be as follows;

=IF(C2-(E2*2.66)<0,0,C2-(E2*2.66))

This will convert the result of the formula to zero, in the event of the LCL being a negative value;

Step 7

That’s the maths over with, now to create a chart – from here I am using instructions specific to Excel 2013 – other versions and other products are still capable of doing this but the menu interactions are slightly different – it should be reasonably self explanatory however.

Step 6 Create the Chart

  • Highlight the whole range of data
  • Goto the Insert Menu and create a Line Chart

Step 8

  • Right click on the Chart, and select “Select Data”
  • Un-select Label, Variation and Mean Variation

Step 9

  • Click OK
  • You now have a basic Control Chart!  You can tinker with the labels and format to suit your taste, I like to make the Upper and Lower Control Limits red and the Mean blue but its just a matter of personal preference

 Step 10

Further Refinements

You can add the 1 and 2 Sigma Limits to the chart too – continue adding the 1 and 2 Sigma Limits to the end of the data set and incorporating them as series on the chart using the formulae in Part 2.

Another nice trick to use, to demonstrate the effect of a change in the system, is to recalculate the Control Limits at a certain point on the Chart.

I will demonstrate how to do both of these in Part 5.

Leave a Reply