Home › Forums › Old Forums › General › CPK formula for Excel?

This topic contains 30 replies, has 23 voices, and was last updated by howe 8 years, 4 months ago.

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

- AuthorPosts
- September 24, 2001 at 4:00 am #20578
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!

September 24, 2001 at 4:00 am #20581David,

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.September 27, 2001 at 4:00 am #20649David,

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.September 27, 2001 at 4:00 am #20658Matt could you email them to me?

September 27, 2001 at 4:00 am #20664If 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.September 27, 2001 at 4:00 am #20665Can you send me these modules as well?

Thanks! tomSeptember 28, 2001 at 4:00 am #20691Can you send me the macro? Thanks, Rene Klerx

September 28, 2001 at 4:00 am #20710Matt:

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

Thanks,

July 16, 2004 at 5:22 pm #63615I have a question about the cpk formula, how can I get the cpk with 6 sigma?

May 26, 2005 at 12:15 pm #83559could someone email the file to me?

June 25, 2005 at 3:59 pm #85829Could you please send me the CPK formula for Excel please?

I’ll appreciate your help.

Thank you.July 22, 2005 at 7:17 pm #87434Matt,

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.July 25, 2005 at 3:18 pm #87532Mark,

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.

MattJuly 25, 2005 at 4:38 pm #87543Matt,

Its markbust1@aol.com

Cheers,

MarkApril 13, 2006 at 6:23 am #103214hi,

PlsApril 13, 2006 at 6:23 am #103215hi,

Pls meApril 14, 2006 at 4:59 pm #103334Matt,

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 FunctionApril 14, 2006 at 5:48 pm #103339I’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)April 14, 2006 at 8:33 pm #103346I 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.

May 8, 2006 at 7:02 pm #104580Here’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 DeviationOctober 16, 2006 at 1:44 pm #113613Dear Matt

Would you please send me the code for CPK calculation in excel?

best regards

Neghabi

October 26, 2006 at 5:49 pm #114697Hello David,

could you send me the Cpk formula that you create in VB?

thanks,

JPOctober 26, 2006 at 5:57 pm #114699Good luck…that post from Matt (not David) is 5 years old.

October 26, 2006 at 6:24 pm #114703Oops, I did not notice that date.

Does somebody has this formula?October 26, 2006 at 7:15 pm #114709David,

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.January 31, 2007 at 6:08 am #121141What 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?January 28, 2008 at 10:14 am #140884How 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)

August 4, 2008 at 4:39 am #149035Dear Matt

I need the code could you please send it to me via mneghabi@tam.co.ir

best regards

neghabiNovember 4, 2008 at 11:46 pm #152494Hi Matt,

Is it possible to send Visual basic program for calculating Cpk using Excel.

thanks

vinay

February 16, 2009 at 12:14 am #156922Need your help how to create cp-cpk formula in excel

June 10, 2009 at 7:07 pm #160826Can you plaease email me the cp cpk module?

- AuthorPosts

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

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 »