# What is the Formula for Cpk in Excel?

L

#### lee01

Can someone inform what the Cpk formula I should be using in excel?

I'm currently using:

=IF(B11<B12,B11/3,B12/3)

Converted: If(0.351<2.921,0.351/3,2.921/3)

I'll tell you what, I'm taking a full sample of points and have these values, anyone want to give me an idea how to calculate the value?0.0166
0.0073
0.0003
0.0078
0.0039
0.0138
0.0033
0.0101
0.0008
0.0009
0.0114
0.0129
0.0021
0.0053
0.0173
0.0139
0.0021
0.0007
0.0118
0.0126
0.0019
0
0.0124
0.0123
-0.0036
0.0145
0.0088
0.0101
0.0068
0.0089
0.0178
0.0014
0.0136
0
0.0138
0.0141
0.0023
0.0143
0.0172
0.0038
0.0084
0.0234
0.0129
0
0.0011
0.0119
0.0111
0.0023
0.0039
0.0092
-0.0018
0.0073
0.0014
0.004
0.0116

Mnay many many thanks

Lee01

C

#### Craig H.

Lee:

Why don't you put the data in an Excel file and attach it to a thread?

I assume the spec is .351 min, 2.921 max?

L

#### lee01

Sorry my friends I’ve got it know!

I have been creating the following statistics in the following way, please can you have a look at the formula’s to make sure I’m on the correct path.

Cell Statistic & Exactly what I’m
Number Result Entering in Excel

B1 USL =0.005 0.005 (From design)
B2 LSL = -0.005 -0.005 (From design)
B3 STD = 0.001 =STDEVP(B15:B63) (note: B15:B63 is the location of the data)
B4 Mean = 0.001 =AVERAGE(B15:B63)
B5 Min = -0.002 =MIN(B15:B63)
B6 Max = 0.004 =MAX(B15:B63)
B7 Range= 0.006 =MAX(B15:B63)-MIN(B15:B63)
B8 UCL = 0.005 =B4+(3*B3)
B9 LCL = -0.003 =B4-(3*B3)
B10 Cp = 1.25 =(B1-B2)/(6*B3)
B11 Cpu = 3.25 =(B1-B4)/(B3*3)
B12 Cpl = 4.251 =(B4-B2)/(B3*3)
B13 Cpk = 1.08 =MIN(B11:B12)

L

#### lee01

Sorry my friends I’ve got it know!

I have been creating the following statistics in the following way, please can you have a look at the formula’s to make sure I’m on the correct path.

Its in the attachment

Lee01

D

#### Darius

Wrong

You are using Total Variation estimate (STDEV) to estimate Control Limits, and you are using Total Variation estimate to calculate Cp, Cpk, Cpu, Cpl.

The index calcualted this way is pp, ppk, ppu, ppl

To calculate the Cp, Cpk, and Control Limits use Moving Range or Averages to estimate the variation.

The Total Variation estimate is affected by the variation between samples and within samples, the estimate for control limits is the within sample variation.

Sigma = MR_average / 1.128 (for individual)
or
Sigma = R_average /d2 for (subgroups <>1)

A

#### Atul Khandekar

Questions...

Lee,

I still don't get it - or maybe I am missing something very obvious!

1.You have posted 55 values, yet the document shows AVERAGE(B15:B63) ie only 48 values.

2. Your computed values are rounded off to 3 decimal digits and the data has 4 decimal digits. There may be some errors due to rounding.

3. I did not try calculting with Excel, but you have so many values above USL (0.005) that Cpk of 1.08 seems unlikely. Then again MIN(3.25,4.251) can't be 1.08 !

4. If by "full sample of points" you mean the entire population, then STDEVP is okay. If not, use STDEV. and yes the index would be Ppk.

L

#### lee01

Okay! Wait a minute.

I understand where your coming from but. . . . .

I'm trying to work out Cpk, & Cp in excel and I have and willl continue to measure the full spopulation (No sample measurement) so I think I'm using the correct Excel calculation.

Is this worng/ correct what should I do? This is being used and has been used for some time in this multinational organisation so are you sure and how will I inform everyone they have been doing it wrong and how they are doing it wrong and how to to do it correct.

Lee01

A

#### Atul Khandekar

Lee,

I'm sure there must be excel spreadsheets for Cp/Cpk posted somewhere on the forum before. You could try to search and look at them.

Alternatively, post your excel file so people can take a look at it and suggest corrections (if any reqd).

Rgds.

L

#### lee01

Okay here it is you’ve got it!

The question is. . . . .

I’m putting point measurement information into MS Excel. Then I’m trying to compute the relevant statistical calculations using formulas within excel, are these okay, are they the correct formulas to use and if not what should I be doing?????????

Another point, these measurement points are the full quantity of measurement points so I’m making the computations full population accordingly! Is this correct?

Many thanks

Lee01

A Cpk Formula: Am I using it correctly? Excel .xls Spreadsheet form Excel .xls Spreadsheet Templates and Tools 14
A Cpk Formula seems off, need help!? Capability, Accuracy and Stability - Processes, Machines, etc. 11
B Formula Data Validation Set for Cp, Cpk, Pp & Ppk Capability, Accuracy and Stability - Processes, Machines, etc. 1
C I need help verifying formula's in a database for LCL UCL Cp Cpk PPk Statistical Analysis Tools, Techniques and SPC 14
Bend allowance formula to calculate per degree per radii Manufacturing and Related Processes 2
Unbiasing Constant "d4" formula Statistical Analysis Tools, Techniques and SPC 4
Formula for Calculating NoGo Major Diameter for UN gages Inspection, Prints (Drawings), Testing, Sampling and Related Topics 4
Excel OOT (Out of Tolerance) Formula Excel .xls Spreadsheet Templates and Tools 3
J CMM Accuracy Formula General Measurement Device and Calibration Topics 3
Formula in the Annex of IEC 61000-4-3 - How is k=7 determined? IEC 60601 - Medical Electrical Equipment Safety Standards Series 2
Inspection Form With Pass/Fail Formula Excel .xls Spreadsheet Templates and Tools 11
S used excel formula calculated the ARL's with Rule1&Rule2 for Shewhart control chart Statistical Analysis Tools, Techniques and SPC 0
Signal-to-Noise (S/N) Ratio formula check Design and Development of Products and Processes 2
S Scrap % Calculation Formula Manufacturing and Related Processes 10
P IF formula - Excel 2003 Excel .xls Spreadsheet Templates and Tools 4
J Bias formula in MSA 4th Edition vs 3rd Edition Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 4
N Formula that will calculate the NoGo Major Diameter of an M20 x 1.5 6H Thread gage Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
Formula to tie together - Confidence/Reliability Level AQL? LTPD? Reliability Analysis - Predictions, Testing and Standards 3
F How to Calculate the unbiased Standard Deviation in Excel using Minitab Formula Capability, Accuracy and Stability - Processes, Machines, etc. 6
M Dimension Report Formula .xlsx Editable Spreadsheet Form Excel .xls Spreadsheet Templates and Tools 24
M Should I use binomial or Poisson distribution formula? Statistical Analysis Tools, Techniques and SPC 6
F Excel I want to use the count I get in another formula Excel .xls Spreadsheet Templates and Tools 13
M Formula for Bell Shape in Excel Using Minitab Software 1
M What is the formula to create a bell curve? Excel .xls Spreadsheet Templates and Tools 8
P Does anyone have the GRR Formula? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 1
M Standard Deviation Estimation Formula - Student Question Statistical Analysis Tools, Techniques and SPC 4
B Attrition Rate and the formula to calculate Attrition Rate Quality Manager and Management Related Issues 1
D Demonstration Reliability Test Formula with allowance for 'x' Failures Reliability Analysis - Predictions, Testing and Standards 6
Is there a formula to link words in Excel sheet to a graph? Excel .xls Spreadsheet Templates and Tools 5
N Linear Regression Formula with Four Variables to a Power Regression Formula Using Minitab Software 2
MSA - According to the formula cg how much tolerance should be put in place? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 1
F SPC Newbie looking for Excel Spreadsheet formula for a sample size of 40 components Capability, Accuracy and Stability - Processes, Machines, etc. 8
A Bias Linearity Stability MSA Excel File with Formula Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 4
Repeatability Standard Deviation Formula in MSA 4th Edition Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 5
M Formula for Prioritizing Engineering Change Requests (ECR)? Document Control Systems, Procedures, Forms and Templates 1
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
Z KPI Formula Issue - "Change Requests Managed" = Changed/Total ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
O Which role plays Inverse cdf of a Standard Normal Distribution in Formula for Z-Bench Using Minitab Software 8
D Route Delivery Efficiency Formula - Miles driven, number of deliveries, and time(hrs) Benchmarking 3
S Formula used in Minitab 16 - "Pooled Standard Deviation" Statistical Analysis Tools, Techniques and SPC 2
X Microsoft Excel 2003 Formula Restrictions Excel .xls Spreadsheet Templates and Tools 9
T Gage R&R Sheet Formula error?! NDC less than 4 Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
C Sample Size Calculation Formula for Process Capability Statistical Analysis Tools, Techniques and SPC 25
Q Accuracy: Is there a specific formula to compute Measurement Device Accuracy? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
Manual Formula for p-value Calculation for MSA Analysis Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 10
D Ring Gauges P.D Formula using "Ball Rounded Teeth" General Measurement Device and Calibration Topics 13
R Formula for calculating Money Value equivalent to 1% FPY (First Pass Yield) Six Sigma 5
B Problem with Reliability and Availability Formula Reliability Analysis - Predictions, Testing and Standards 8
N Usability Formula to Predict Use Error Reduction IEC 62366 - Medical Device Usability Engineering 6