Home › Forums › Old Forums › General › CPK formula for Excel?
This topic contains 30 replies, has 23 voices, and was last updated by howe 7 years, 7 months ago.
Could someone please let me know if there is a formula for calculating the CPK in Excel, and if so, what the formula is, I would really appreciate it!
David,
You can use the following formula:
=MIN((A3-A1)/(3*A2),(A1-A4)/(3*A2))
where A1 is the cell that contains the process average
A2 is the cell that contains the process standard deviation
A3 is the cell that contains the Upper Spec Limit
A4 is the cell that contains the Lower Spec Limit.
Of course, be careful when you have a one sided tolerance. In that case, use only the appropriate half of the calculation.
For example for a max tolerance (i.e. 0.005 Flatness): use only Upper Spec half of the calculation
=(A3-A1)/(3*A2)
Hope this helps.
David,
I wrote two visual basic for applications User-Defined functions that calculate Cp and Cpk just like any other formula in Excel. By attaching the visual basic module to a new, blank worksheet and saving this worksheet as your start-up template (see Excel’s online help for detailed instructions), you can have these formula readily available in every new worksheet you make.
The only downside to this approach is the fact that user-defined functions are viewed as macros by Excel’s macro-checking device. Excel will catch the defined functions (as macros) everytime you, or someone else, opens your worksheets if you have this macro-checking option set.
If interested, I can email you the .bas Visual Basic module containing the user-defined functions. You simply import this module using Visual Basic Editor under the Tools…Macro menu. The module is “attached” to the worksheet. Then save as a template.
The functions are recognized by Excel’s Paste Function (the “fx” button on the main toolbar). This is helpful the first few times you use them in order to learn the arguments (cell references) necessary to perform the calculations.
Matt could you email them to me?
If you want me to email you a copy of the VBA module containing my Cp and Cpk user-defined functions, please send a separate request via email to matt.stough@sylvania.com.
Please, include your email address explicitly. Thank you.
Can you send me these modules as well?
Thanks! tom
Can you send me the macro? Thanks, Rene Klerx
Matt:
I would appreciate it if you would email it to me as well!
Thanks,
I have a question about the cpk formula, how can I get the cpk with 6 sigma?
could someone email the file to me?
Could you please send me the CPK formula for Excel please?
I’ll appreciate your help.
Thank you.
Matt,
I know its been a while but is there any chance you can e-mail me the visual basic information. Im currently using SPC to chart this data but having this information would make life so much easier.
Cheers,
Mark.
Mark,
If you send me your email address, I can forward the code. I will forward it as text only for you to place in a macro in Excel. Recent email security devices strip out attachments with macro code.
Matt
Matt,
Its markbust1@aol.com
Cheers,
Mark
hi,
Pls
hi,
Pls me
Matt,
Did you use Excel functions in your code or did you use VBA functions? I tried to use VBA but could not find STDEV code to use.
This is how I wote mine:
Function CPK(Range As Range, USL, LSL)Dim Cpl As VariantDim Cpu As VariantDim AR As VariantDim DR As Variant
AR = Application.WorksheetFunction.Average(Range)DR = Application.WorksheetFunction.StDev(Range)
Cpl = (AR – LSL) / (3 * DR)Cpu = (USL – AR) / (3 * DR)CPK = Application.WorksheetFunction.Min(Cpl, Cpu)
End Function
I’ve had many, many requests for this macro, which is VBA/User-Defined Function in methodology. Here’s the text to copy to a macro page for your use because email servers have made if vitually impossible to mail something with any kind of attachment (.zip, .bas, etc.)
I store the macro in a hidden Excel file placed inside the XLStart folder so that every time I run Excel (on my computer), the user-defined functions are available to any workbook/worksheet; however, if you send it to someone else without the macro (user-defined function) included in that file, the function won’t work any longer.
Here’s the code…
” Statistics Functions common in manufacturing and absent as built-in’ Excel functions are defined below. These are generally available’ in dedicated statistics packages and most probably in commercially’ available add-on modules to Excel.” programmed by Matthew A. Stough’ June 13, 1998′
Function Cp(Target As Single, Tolerance_± As Single, Process_Sigma As Single) As Single USL = Target + Tolerance_± LSL = Target – Tolerance_± Cp = (USL – LSL) / (6 * Process_Sigma)End Function
Function Cpk(Target As Single, Tolerance_± As Single, Process_Mean As Single, Process_Sigma As Single) As Single USL = Target + Tolerance_± LSL = Target – Tolerance_± CPU = (USL – Process_Mean) / (3 * Process_Sigma) CPL = (Process_Mean – LSL) / (3 * Process_Sigma) If CPU < CPL Then Cpk = CPU Else Cpk = CPL End IfEnd Function
You call either function by it’s definition in the “Function” line of the code. For example, =Cp(6,0.5,0.2)
I don’t see the point in having to key in your average, sigma, upper control limit, and lower control limit. With a little more key strokes you could write the formula. The only advantage I see is not having to remember the formula.
Here’s an excel formula that handles one-sided spec limits:
(copy and paste it)
=IF(AND(ISBLANK(B6),ISBLANK(B7))=TRUE,”no spec limits”,IF(ISBLANK(B6)=TRUE,((B9-B7)/(3*B10)),IF(ISBLANK(B7)=TRUE,((B6-B9)/(3*B10)),MIN((B6-B9)/(3*B10),(B9-B7)/(3*B10)))))
Where:
B6=USL
B7=LSL
B9=Mean
B10=Standard Deviation
Dear Matt
Would you please send me the code for CPK calculation in excel?
best regards
Neghabi
Hello David,
could you send me the Cpk formula that you create in VB?
thanks,
JP
Good luck…that post from Matt (not David) is 5 years old.
Oops, I did not notice that date.
Does somebody has this formula?
David,
You can use the following formula:
=MIN((A3-A1)/(3*A2),(A1-A4)/(3*A2))
where A1 is the cell that contains the process average
A2 is the cell that contains the process standard deviation
A3 is the cell that contains the Upper Spec Limit
A4 is the cell that contains the Lower Spec Limit.
Of course, be careful when you have a one sided tolerance. In that case, use only the appropriate half of the calculation.
For example for a max tolerance (i.e. 0.005 Flatness): use only Upper Spec half of the calculation
=(A3-A1)/(3*A2)
Hope this helps.
What if the USL, the mean or the LSL (or any combination of these) are
negative? What happens to the sign and the value of Cpk?
And is it then |Cpk| (the absolute value of Cpk) that should be > 1.33?
How To Use Formulas On Excel File
CPK
=
Round (Min((USL-X) / (3* Sigma), (X – LSL) / (3* Sigma)),2)
Min
=
Min (From cell # : To Cell #)
Max
=
Max (From cell # : To Cell #)
Range
=
(Max (From cell # : To cell#) – Min (From cell # : To cell #))
Sigma
=
Round (STDEV(From cell # : To cell #), 4)
3 Sigma
=
Round (3*Sigma,4)
Average
=
Round (Average (From cell # ; To cell #), 4)
Dear Matt
I need the code could you please send it to me via mneghabi@tam.co.ir
best regards
neghabi
Hi Matt,
Is it possible to send Visual basic program for calculating Cpk using Excel.
thanks
vinay
Need your help how to create cp-cpk formula in excel
Can you plaease email me the cp cpk module?
The forum ‘General’ is closed to new topics and replies.
© Copyright iSixSigma 2000-2017. User Agreement. Any reproduction or other use of content without the express written consent of iSixSigma is prohibited. More »