CPK formula for Excel?

Six Sigma – iSixSigma Forums Old Forums General CPK formula for Excel?

Viewing 31 posts - 1 through 31 (of 31 total)
• Author
Posts
• #68881

TomF
Member

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.

0
#27878

Edwards
Participant

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!

0
#68935

Savage
Participant

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.

0
#68943

Bob Belden
Participant

Matt could you email them to me?

0
#68946

Savage
Participant

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 [email protected].

0
#68947

Tom Hallaron
Member

Can you send me these modules as well?
Thanks! tom

0
#68968

Rene Klerx
Participant

Can you send me the macro? Thanks, Rene Klerx

0
#68985

Greg Pesina
Participant

Matt:
I would appreciate it if you would email it to me as well!

Thanks,

0
#103605

SERGIO CHAVEZ
Member

I have a question about the cpk formula, how can I get the cpk with 6 sigma?

0
#120239

Bart
Participant

could someone email the file to me?

0
#122174

Juan Ortiz
Participant

Thank you.

0
#123501

Savage
Participant

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.

0
#123587

Savage
Participant

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

0
#123596

Savage
Participant

Matt,
Its [email protected]
Cheers,
Mark

0
#136314

bcthor
Participant

hi,
Pls

0
#136315

bcthor
Participant

hi,
Pls me

0
#136403

ezweld
Participant

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

0
#136408

M.A.Stough, Ph.D.
Participant

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)

0
#136414

ezweld
Participant

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.

0
#137409

Jon Cohen
Participant

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

0
#144874

neghabi
Participant

Dear Matt
Would you please send me the code for CPK calculation in excel?
best regards
Neghabi

0
#145829

Juan Mexia
Participant

Hello David,
could you send me the Cpk formula that you create in VB?
thanks,
JP

0
#145831

Jered Horn
Participant

Good luck…that post from Matt (not David) is 5 years old.

0
#145833

Juan Mexia
Participant

Oops, I did not notice that date.
Does somebody has this formula?

0
#145838

Help for the handicapped
Participant

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.

0
#151302

Jim Neef
Participant

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?

0
#167833

Shaji Renic Bethel
Member

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)

0
#174526

neghabi
Participant

Dear Matt
I need the code could you please send it to me via [email protected]
best regards
neghabi

0
#177389

Vinay
Member

Hi Matt,
Is it possible to send Visual basic program for calculating Cpk using Excel.
thanks

vinay

0
#181211

Amran
Participant

Need your help how to create cp-cpk formula in excel

0
#184522

howe
Participant

Can you plaease email me the cp cpk module?

0
Viewing 31 posts - 1 through 31 (of 31 total)

The forum ‘General’ is closed to new topics and replies.