The control chart is one of the Six Sigma set of management charts and is used to show when a process is deviating from its expected behaviour. Points that are outside the expected range can be highlighted to indicate that there might be a problem with those samples.
The same data as would be used for a typical line chart can be used to create a control chart – simply a time-series of the variable under review, with or without an associated date column. In the Settings for the chart, you can choose how many samples of data you want to set as the reference period. In the chart below, the first 20 periods have been used to define the expected mean and standard deviation of the data. Points that sit outside that band are shown in red while those inside are shown in green.
By changing the width of the “acceptable” band, you can change which points are flagged for attention.
QuickChart offers four standard options for defining the band of acceptable values:
- Mean + a multiple of standard deviations
- Median + a multiple of the interquartile range
- Mean, max and min
- Custom thresholds
The thresholds on the single-click charts will be automatically calculated according to the chosen setting (see Settings below).
The single-click Control Chart is not “live” – the reference lines and the colours of the points will not change with changing data. To achieve this. use the “=QC.CONTROL(…)” formula. The style of chart that is produced is slightly different from the single-click charts.
This formula allows a series to be plotted together with a number of reference lines. The highest and lowest of the reference lines are used to set the thresholds at which point the indicators change.
The formula takes two parameters – the source data, identical to that used to make the single-click version, and a table of Reference lines. These can be constants or they can be the output from a formula, e.g., “=AVERAGE(…)”.
The output of the array formula is the same height as the source time-series (including the title row). The number of columns is equal to four plus the number of reference points.
Like most of the other QuickChart formulae, this is an array formula, so you must use “Ctrl-Shift-Enter” when you complete it, and select the full range required (although the most recent versions of Excel provide much better support for array formulae). More details on array formulae here.
To make the Control Chart just click within the formula array and press “Control” on the Excel ribbon toolbar. Alternatively, if you right-click in the range then the command “Make Control Chart” should be at the top of the context menu.
Most of the settings are only relevant to the one-click option as the formula option requires you to set your own threshold values. The “Highlight points” checkbox applies to both versions.
- Reference Lines – The drop down offers the four options for the thresholds described above.
- Mean + a multiple of standard deviations (default is three * the standard deviation)
- Median + a multiple of the interquartile range (default is 1.5 * the IQR)
- Mean, max and min
- Custom thresholds
- The “Valid Above” and “Valid Below” boxes are enabled with the custom option. These allow you to set custom threshold values. Usually the “Valid Above” number will be less than the “Valid Below” number, creating a range between these two values that is considered “normal”. Alternatively, if the “Valid Above” number is the greater of the two then the in-between range will be considered abnormal.
- The multiplier value is applied to the set threshold values, e.g., if “Mean and Standard Deviations” is selected in the drop-down then the multiplier will specify how many standard deviations are allowed in the acceptable range.
- The auto-adjust checkbox enables QuickChart to automatically change the multiplier value according to the drop-down defaults.
- The sample period sets the number of points from the start of the time-series that define “normal”.
- The “Full Period” checkbox will use all of the samples to define normal behaviour and outlier points within the series can be identified.
- “Highlight Points” uses green and red dots (or other colours as defined in the formatting template) to show which samples are considered “normal” and “abnormal”
- “Show Sample End” adds a vertical line to the chart to illustrate where the sample period ends.