Yes, it’s easier to prepare the control charts using Minitab. However, what if you move into organization that does not have minitab? Would you ask them to procure minitab even before telling the management the magnitude of the problem? Or would you rather use your knowledge to prepare control charts in excel? I believe you would utilize this opportunity to showcase your knowledge and not be dependent upon a tool.
So what are control limits? Control limits, also known as natural process limits, are horizontal lines drawn on a statistical process control chart, usually at a distance of ±3 standard deviations of the plotted statistic from the statistic’s mean. Before we proceed further and deep dive into calculation of control limits, let me distinguish between control limits and specification limits. Do not confuse control limits with specification limits. Control limits are based on process variation. Specification limits are based on customer requirements. A process can be in control and yet not be capable of meeting specifications. Control limits are the horizontal lines above and below the center line that are used to judge whether a process is out of control. The upper and lower control limits are based on the random variation in the process. We (and all tools in market) often use 3sigma as the control limit. Logic behind that is simple. Statistically for normally distributed continuous data the area bracketed by the control limits will on average contain 99.73% of all the plot points on the chart, as long as the process is and remains in statistical control.
Now, since have good understanding of what is Control limits, let’s look at how do we calculate control limits for continuous and discrete data.
For continuous data: UCL: Mean+3Sigma and LCL: Mean-3Sigma.
Where Sigma is standard deviation of data. Standard deviation can be calculated in excel with formula: Stdev.p (if you have population data) or stdev.s (if you have sample data). Only difference between these 2 formulas is denominator is either N or N-1 for calculation in excel.
For Discrete Data: there are 2 situations here. Either you are counting defects or looking at defectives.
- Below is how you calculate UCL and LCL if you are capturing defectives:
UCL: Percent Defective+3sigma and LCL: Percent Defective-3Sigma
Where Sigma is standard deviation of data. Standard deviation is calculated using this formula: Square root((Percent Defective-(1-Percent Defective)/Sample Size)
E.g.: ABC Company wants to identify number of defective bulbs they make. They determine that average percentage defective in sample of 100 is 2%. For this example lets calculate UCL and LCL for P chart (control chart for this type of data).
Now using above data UCL will be: 0.02+(3*0.0140) which is 0.062 and LCL will be 0.02-(3*0.0140) which is -0.022. Since the value is less than 0, LCL will be converted to 0.
- Below is how you calculate UCL and LCL if you are capturing Defects:
UCL: Mean of Number of Defects + 3 multiplied by square root of mean of Number of Defects and LCL is calculated as Mean of Number of Defects – 3 multiplied by square root of mean of Number of Defects.
E.g. ABC Company wants to identify number of defects in painted car panels they make. They determine that average defects in sample of 100 is 2. For this example lets calculate UCL and LCL for P chart (control chart for this type of data).
UCL is 2+3*(SQRT(2))= 6.24 and LCL is 2-3*(SQRT(2))= -2.24. Since the value is less than 0, LCL will be converted to 0.