FRIDAY, JULY 25, 2014
Font Size
Topic CPK formula for Excel?

CPK formula for Excel?

Home Forums Old Forums General CPK formula for Excel?

This topic contains 30 replies, has 1 voice, and was last updated by Avatar of Michael howe howe 5 years, 1 month ago.

Viewing 31 posts - 1 through 31 (of 31 total)
  • Author
    Posts
  • #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!

    #20581

    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.

    #20649

    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.

    #20658

    Matt could you email them to me?

    #20664

    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.

    #20665

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

    #20691

    Can you send me the macro? Thanks, Rene Klerx

    #20710

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

    #63615

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

    #83559

     could someone email the file to me?

    #85829

    Could you please send me the CPK formula for Excel please?
    I’ll appreciate your help.
     
    Thank you.

    #87434

    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.

    #87532

    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

    #87543

    Matt,
    Its markbust1@aol.com
    Cheers,
    Mark

    #103214

    hi,
    Pls

    #103215

    hi,
    Pls me

    #103334

    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

    #103339

    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)

    #103346

     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.

    #104580

    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

    #113613

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

    #114697

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

    #114699

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

    #114703

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

    #114709

    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.

    #121141

    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?

    #140884

    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)

     
     
     

    #149035

    Dear Matt
    I need the code could you please send it to me via mneghabi@tam.co.ir
    best regards
    neghabi

    #152494

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

    #156922

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

    #160826

    Can you plaease email me the cp cpk module?

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

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

Login Form