# Calculating LCL, UCL, Cp, and Cpk in an Excel Spreadsheet

#### TarKEpa

##### Registered
Hi All;

Kindly I need to make sure from my calculation in the attached sheet

1. I'm calculating the UCL and LCL for Product Overall Yield (Pharmaceutical), I used I-MR chart,

2. then, calculate Cp and Cpk. (This is before Improvement)

============================================================

1. I got rid of (Out-of-Specs) and (Out-of-Control) points,

2. then, calculate again new UCL and LCL using the old UCL and LCL as a new USL and LSL,

3. then, calculate Cp and Cpk again (after Improvement)

My Questions:
1. Did I use the correct control chart?
2. Am I right in my steps to calculate UCL and LCL before and after improvement?
3. Am I right in calculating Cp and Cpk before and after improvement? and If, Why Cp and Cpk before improvement is higher than after?????

#### Attachments

• 299 KB Views: 3,489
Last edited:

#### Bev D

##### Heretical Statistician
Staff member
Super Moderator
First you calculate the control limits without removing out of spec points. There is no statistical or physics foundation for doing this. removing points that are out of specification should never be done.

Second the removal of out of control points for the calculation of control limits is not normally necessary although it can be done carefully. It is usually OK if you are only removing one or two that you know the assignable cause for. otherwise you might get control limits that are just too narrow to be useful...

Certainly you never remove OOC or Out of Spec points when calculating capability including Cp/Cpk indices...

However, in this case the control limits calculated with all of the data are more valuable as they demonstrate that you have an unstable process - normally, this would preclude the usefulness of any Cp or Cpk calculation (beyond their inherent useless properties)

Technically the data you are plotting is categorical data so your calculation of the standard deviation (for Cp/Cpk) is incorrect as you used the continuous data formula. However, Yields are weird data and this approach is probably close enough for an estimate...

HOWEVER, why? Specifications on Yields are silly. They aren't specifications in the real sense of the term, they are really goals. (and an upper goal on yield means nothing) It might be nice to understand the capability of the process yield vs. the desired yield goal, but Cp and Cpk only confuse the matter. A simple control chart with control limits and the minimum goal should suffice and it will provide far more insight than a simple index ever can. If you need some quantification of the capability a simple calculation of the proportion of times the process exceeds the goal will suffice and it is probably far more understandable to those who care than Cp/Cpk will be.

By the way, you shouldn't smooth the line on the charts. While those who don't know anything about data will think it's 'pretty', a smoothed line implies that there is data between the plotted data points and in this case there isn't. graphical design standards are to use a simple straight line between the points to display the time series...

#### TarKEpa

##### Registered
Thanks Bev, I really appreciate that, but excuse me I will try to understand each point separately.
and generally, I need to know, with this data collection, what do you prefer which types of control charts can I use? and how can I calculate CLs?

First you calculate the control limits without removing out of spec points. There is no statistical or physics foundation for doing this. removing points that are out of specification should never be done.
Yes Bev, I do that (In my first chart), I calculate the CL without removing out-of-specs points using 2 sigma (X bar (+)(-) mR bar * 1.77).

My specs are 80% lower and 105% upper.

Second the removal of out of control points for the calculation of control limits is not normally necessary although it can be done carefully. It is usually OK if you are only removing one or two that you know the assignable cause for. otherwise you might get control limits that are just too narrow to be useful...
In 2nd chart, I already removed out-of-specs & OOC, otherwise how can I Improve my process? I wonder?
If I have LCL= 91.6% (in 1st chart using 2 sigma), How can I raise or narrow this limit (I need to reduce scrap in process) without removing any of outliers points?

Certainly you never remove OOC or Out of Spec points when calculating capability including Cp/Cpk indices...

However, in this case the control limits calculated with all of the data are more valuable as they demonstrate that you have an unstable process - normally, this would preclude the usefulness of any Cp or Cpk calculation (beyond their inherent useless properties)

Technically the data you are plotting is categorical data so your calculation of the standard deviation (for Cp/Cpk) is incorrect as you used the continuous data formula. However, Yields are weird data and this approach is probably close enough for an estimate...

HOWEVER, why? Specifications on Yields are silly. They aren't specifications in the real sense of the term, they are really goals. (and an upper goal on yield means nothing) It might be nice to understand the capability of the process yield vs. the desired yield goal, but Cp and Cpk only confuse the matter. A simple control chart with control limits and the minimum goal should suffice and it will provide far more insight than a simple index ever can. If you need some quantification of the capability a simple calculation of the proportion of times the process exceeds the goal will suffice and it is probably far more understandable to those who care than Cp/Cpk will be.

Great, so what formula can I use to calculate std. deviation and Cp, Cpk? I have more questions to ask regarding this, but I'm trying to get that point first By the way, you shouldn't smooth the line on the charts. While those who don't know anything about data will think it's 'pretty', a smoothed line implies that there is data between the plotted data points and in this case there isn't. graphical design standards are to use a simple straight line between the points to display the time series...
You're right, #### Bev D

##### Heretical Statistician
Staff member
Super Moderator
I will try to understand each point separately. and generally, I need to know, with this data collection, what do you prefer which types of control charts can I use? and how can I calculate CLs?
As I said in my first response, the I, MR chart is probably the best choice for Yields. (I use it quite successfully for such data). Here is an article by Donald Wheeler on the I,MR chart that I recommend you read: “What About p Charts?” (It’s free, just click the article title, it’s linked to the article)

I calculate the CL without removing out-of-specs points using 2 sigma (X bar (+)(-) mR bar * 1.77).
Whoa. I didn’t check your control limit calculations. ALWAYS use 3-sigma limits. So the I, MR multiplier is 2.66 not 1.77. 3 sigma limits are used because they provide the best balance between false alarms and misses when monitoring data over time. (Control charts aren’t ‘hypothesis test’ so the typical 95% level used for hypothesis tests is NOT appropriate for control charts…) Here is another article by Donald Wheeler that explains why 3-sigma limits are used for control charts: “Why Three-Sigma Limits?” (again it’s free)

My specs are 80% lower and 105% upper.
Your specs are just lines drawn in space…they have no basis in reality. First yields can’t be 105%. Again a minimum GOAL might be necessary for improvement reasons but they are not specifications…

In 2nd chart, I already removed out-of-specs & OOC, otherwise how can I Improve my process? I wonder? If I have LCL= 91.6% (in 1st chart using 2 sigma), How can I raise or narrow this limit (I need to reduce scrap in process) without removing any of outliers points?
I don’t understand this…the only way to improve the process is to actually improve it. Determine the physical causes of yield loss, then eliminate, reduce or control for them such that your yields actually improve. Simply removing data from calculations doesn’t change anything. Those values actually occurred and they will occur again in the future…you can recalculate the control limits once the process actually improves…

…so what formula can I use to calculate std. deviation and Cp, Cpk? I have more questions to ask regarding this, but I'm trying to get that point first
Don’t do it. Cp and Cpk don’t tell you anything. This isn’t a math exercise. This is about physics and thinking.
Just LOOK at the time series chart of Yields with the control limits and THAT tells you directly what the process is doing. You can directly calculate the amount of scrap or the proportion of times the process yields less than the GOAL, if you need a number to quantify this. But Cp and Cpk are not valuable or useful in this situation. Ask yourself what you need to know about this process and then select the best analysis.

K Calculating UCL (Upper Control Limit) and LCL (Lower Control Limit) Statistical Analysis Tools, Techniques and SPC 14
J Cumulative Count of Conforming Control Chart (CCC) - Calculating UCL and LCL Statistical Analysis Tools, Techniques and SPC 8
True position, calculating theoretical distances General Measurement Device and Calibration Topics 0
Lean: Conducting Capacity Study, calculating Cycle times on laser cutting machines Lean in Manufacturing and Service Industries 2
Calculating Carbon Footprint in the Organization Miscellaneous Environmental Standards and EMS Related Discussions 2
Calculating your KPIs ISO 13485:2016 - Medical Device Quality Management Systems 7
Calculating Heat Dissipation Manufacturing and Related Processes 1
Calculating Defect Rates ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 4
Formula for Calculating NoGo Major Diameter for UN gages Inspection, Prints (Drawings), Testing, Sampling and Related Topics 4
Calculating a weight for a machine for the CE label CE Marking (Conformité Européene) / CB Scheme 0
Remote Support - Calculating Number of Employees IATF 16949 - Automotive Quality Systems Standard 2
Calculating Tolerance of a Panel Meter with a 2 degrees of an Arc General Measurement Device and Calibration Topics 0
Product Development - When to start calculating Process Capability Capability, Accuracy and Stability - Processes, Machines, etc. 3
Calculating Reliability for Subsystems in Series Reliability Analysis - Predictions, Testing and Standards 15
Calculating (3rd Party) Audit Days for Company with Seasonal Employees General Auditing Discussions 3
T Formulas for Calculating Coefficients for RTD / PRTD Calibration According to ITS-90 General Measurement Device and Calibration Topics 5
J Calculating part variation from historic data for GRR study Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 1
P Purpose of calculating Ta, Tb & R Square in Linearity Study Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 1
P Purpose of calculating Uncertainty value in calibration study Measurement Uncertainty (MU) 5
M Calculating MTBF for plug-in PCI cards Reliability Analysis - Predictions, Testing and Standards 2
J Calculating impact force, expressed in lbs, of a 5 lb weight dropped 48 inches Inspection, Prints (Drawings), Testing, Sampling and Related Topics 1
M Calculating Cpk when sample size equals to 1 Capability, Accuracy and Stability - Processes, Machines, etc. 12
D Calculating Cpk on Non-Normal Data Distribution Capability, Accuracy and Stability - Processes, Machines, etc. 10
K Calculating Capability of a process when data is skewed in the upper range Capability, Accuracy and Stability - Processes, Machines, etc. 4
A Calculating Accuracy for a Multimeter General Measurement Device and Calibration Topics 1
M Calculating Coefficients for an RTD (Resistance Temperature Detector) Probe General Measurement Device and Calibration Topics 3
T Calculating Plating/Coating Weight for IMDS RoHS, REACH, ELV, IMDS and Restricted Substances 3
D Calculating Lower and Upper 3 Sigma Control Limits Statistical Analysis Tools, Techniques and SPC 2
M Calculating Adequate Receiving Inspection Sample Size Statistical Analysis Tools, Techniques and SPC 2
M Calculating Capability of Delivery Performance Capability, Accuracy and Stability - Processes, Machines, etc. 5
K Advice on Calculating Control Chart Control Limits Statistical Analysis Tools, Techniques and SPC 13
B Calculating Combined DPMO and Sigma Level for Two or More Different Work Areas Six Sigma 3
G Calculating the Fraction Defective Confidence Interval for a Lot Statistical Analysis Tools, Techniques and SPC 2
A Calculating Combined Measurement Uncertainty - VDA 5 Measurement Uncertainty (MU) 1
B Calculating Precision to Tolerance Ratio Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 9
D DPPM - Calculating the Defect Rate of a Software Test Statistical Analysis Tools, Techniques and SPC 9
L FMEA - Calculating RPN = S*O*D FMEA and Control Plans 3
I Shewhart Constants vs Central Limit Theorem in calculating Control Limits Statistical Analysis Tools, Techniques and SPC 18
B Please share a template for calculating Cp Cpk Document Control Systems, Procedures, Forms and Templates 3
S Is a Stable Process (within Control Limits) required for Calculating Pp, Ppk? Capability, Accuracy and Stability - Processes, Machines, etc. 6
Calculating the IATF TS 16949 Recertification Audit Man Days - Clarification ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 6
IEC60825 - Laser Safety - Calculating Maximum Permissible Exposure Correctly Other ISO and International Standards and European Regulations 1
P Calculating Process Capability from Data of attached spreadsheet Statistical Analysis Tools, Techniques and SPC 4
I AQL Formula in Excel - Calculating Sample Size based on an AQL Chart AQL - Acceptable Quality Level 8
D Formula for Calculating Warp Measurement of Corrugated Boxes General Measurement Device and Calibration Topics 1
S Need Help Calculating and Implementing Cs - Wright's Index Statistical Analysis Tools, Techniques and SPC 11
S Calculating an Uncertainty Budget for an Optical Comparator General Measurement Device and Calibration Topics 4
D One-Two-Three Sigma Control Limits - Calculating Control Limits for X-bar Charts Statistical Analysis Tools, Techniques and SPC 3
K Calculating (Approximate) Parallelism Inspection, Prints (Drawings), Testing, Sampling and Related Topics 10
S Calculating MTBF (Mean Time Between Failures) for the whole Production Unit Reliability Analysis - Predictions, Testing and Standards 2