iSixSigma

Cpk – Excel vs Minitab results

Six Sigma – iSixSigma › Forums › Old Forums › General › Cpk – Excel vs Minitab results

  • This topic has 16 replies, 10 voices, and was last updated 13 years ago by Ron.
Viewing 17 posts - 1 through 17 (of 17 total)
  • Author
    Posts
  • February 12, 2008 at 12:19 pm #49329

    Graham Leigh
    ★ 10 Years ★
    Participant

    Hello
     
     
    Please please can you help
     
    Have worked out Cpk long hand in excel – Cpk = 0.71 see below
     
    And then used Minitab – Cpk = 1.03
     
    And get two quite different results – why ?
     

    PART NUMBER
    DIAMETER 47.95-48.10

    1
    47.951

    2
    47.971

    3
    47.970
    Mean (A1)
    47.97623333

    4
    47.986
    Standard Error
    0.002248209

    5
    47.974
    Median
    47.974

    6
    47.971
    Mode
    47.966

    7
    47.966
    Standard Deviation (A2)
    0.012313949

    8
    47.970
    Sample Variance
    0.000151633

    9
    47.968
    Kurtosis
    -0.552924373

    10
    47.959
    Skewness
    0.343703993

    11
    47.968
    Range
    0.049

    12
    47.977
    Minimum
    47.951

    13
    47.977
    Maximum
    48

    14
    47.974
    Sum
    1439.287

    15
    47.975
    Count
    30

    16
    47.978
    Largest(1)
    48

    17
    47.987
    Smallest(1)
    47.951

    18
    48.000
    Confidence Level(95.0%)
    0.004598104

    19
    47.991
    USL (A3)
    48.100

    20
    47.996
    LSL (A4)
    47.950

    21
    47.966
    Cpk
    0.710

    22
    47.963

    23
    47.970
    Cpk =MIN((A3-A1)/(3*A2),(A1-A4)/(3*A2))

    24
    47.975
    where A1 is the cell that contains the process average

    25
    47.966
    A2 is the cell that contains the process standard deviation

    26
    47.992
    A3 is the cell that contains the Upper Spec Limit

    27
    47.992
    A4 is the cell that contains the Lower Spec Limit.

    28
    47.966

    29
    47.998

    30
    47.990

    0
    February 12, 2008 at 1:15 pm #168475

    Van Loon
    ★ 10 Years ★
    Participant

    You have calculated Ppk, not Cpk, in Excel.
    That coresponds fine with Minitab.
    Maybe the big diff. in Cpk and Ppk you get is due to
    your data not beeing all that normal. (AD p.value 0,04)
    Jan
     

    0
    February 12, 2008 at 1:32 pm #168477

    Van Loon
    ★ 10 Years ★
    Participant

    On the other hand, after viewing your data in a chart
    the diff. in Cpk and Ppk is due to a trend in your data.
    Jan

    0
    February 12, 2008 at 1:41 pm #168479

    Graham Leigh
    ★ 10 Years ★
    Participant

    Thanks Jan
    Do you have the correct excel formula please ?
    ta
    Graham
     

    0
    February 12, 2008 at 1:54 pm #168481

    Graham Leigh
    ★ 10 Years ★
    Participant

    Jan
    Thanks again, please tell me correct excel formula for Cpk instead of Ppk
    Thanks
    Graham
     
     
     

    0
    February 12, 2008 at 2:33 pm #168487

    Bower Chiel
    ★ 10 Years ★
    Participant

    Hi Graham
    Use the estimate of standard deviation obtained via the mean moving range method. (See below.)  With only one part measured no moving range can be calculated.  Once two have been measured the formula ABS(B3-B2) in cell C3 gives the first moving range of 0.02.  Copy this formula down the column to get all 29 moving ranges.  The mean moving range divided by the constant d2 (1.128 for ranges based on samples of two measurements) gives an estimate of the process sigma which leads to the value you got from Minitab.  It refers to this estimate of sigma as StDev(Within).  The estimate of sigma you used is essentially what it refers to as StDev(Between).  Have a look at what lies behind the Estimate button and use the Help button to drill down to Methods and Formulae for all the gory details.

    Part
    Diam.
    Moving Range

    1
    47.951
    *

    2
    47.971
    =ABS(B3-B2)

    3
    47.97
    =ABS(B4-B3)

    4
    47.986
    =ABS(B5-B4)

    5
    47.974
    =ABS(B6-B5)

    6
    47.971
    =ABS(B7-B6)

    7
    47.966
    =ABS(B8-B7)

    8
    47.97
    =ABS(B9-B8)

    9
    47.968
    =ABS(B10-B9)

    10
    47.959
    =ABS(B11-B10)

    11
    47.968
    =ABS(B12-B11)

    12
    47.977
    =ABS(B13-B12)

    13
    47.977
    =ABS(B14-B13)

    14
    47.974
    =ABS(B15-B14)

    15
    47.975
    =ABS(B16-B15)

    16
    47.978
    =ABS(B17-B16)

    17
    47.987
    =ABS(B18-B17)

    18
    48
    =ABS(B19-B18)

    19
    47.991
    =ABS(B20-B19)

    20
    47.996
    =ABS(B21-B20)

    21
    47.966
    =ABS(B22-B21)

    22
    47.963
    =ABS(B23-B22)

    23
    47.97
    =ABS(B24-B23)

    24
    47.975
    =ABS(B25-B24)

    25
    47.966
    =ABS(B26-B25)

    26
    47.992
    =ABS(B27-B26)

    27
    47.992
    =ABS(B28-B27)

    28
    47.966
    =ABS(B29-B28)

    29
    47.998
    =ABS(B30-B29)

    30
    47.99
    =ABS(B31-B30)

    Mean Moving Range

    =AVERAGE(C3:C31)

    Constant d2

    1.128

    Estimate of sigma

    =C32/C33
     
    Best Wishes
     
    Bower Chiel

    0
    February 12, 2008 at 2:50 pm #168490

    Graham Leigh
    ★ 10 Years ★
    Participant

    Chiel
    Thanks but I no understandee. Please could you send excel sheet to [email protected]
    Ta
    Graham

    0
    February 12, 2008 at 9:13 pm #168502

    Ron
    ★ 10 Years ★
    Member

    The basic problem is the methodology you utilizedto calcualte everything. This is a large issue for those not truly understanding of the mathmatics behind the calculations.
    You did everything correct mathematically but not statistically.
    Process capability looks at the difference from the first reding to the second, from thesecondto third etc. That is why the order of manufacturer is important to maintain.
    Look at your standrard deviation and that calculated by minitab thge two are not the same.
    You used a software to calculate SD by a classical method then plugged it into a standard equation. This does not work in reality.
    When you select a subgroup size of 1 minitab calculate standard deviation using a moving range methodology, and adjacent observations are treated as subgroups.
    Within subgroup capability represents how the proces could perform if all variation were removed
    The Ppk would represent your data
     
     

    0
    May 6, 2009 at 8:25 pm #183923

    mehmet
    ★ 10 Years ★
    Participant

    your excel result is correct. it will be subgroupsize  is 30 in minita, so cpk result is 0.7

    0
    May 7, 2009 at 11:22 am #183936

    Rick Haynes
    ★ 10 Years ★
    Member

    First of all, the responses you got were fine about the math, but everyone misses the real point here.
    You should not be calculating Cpk at all on this data.
    Any use of a capability metric on a process that is not known to be stable is of no use.  None at all.  This is the first rule of all capability assessments. 
    Cp and Cpk are only reasonable capability assessments on normally distributed data.  So never use them on non-normal data.
    Most users of Cp and Cpk do not understand the underlying assumptions.  Read up on the theory and you will see what I am talking about.  One of the commenters showed how you can calculate the standard deviation from a moving range rather than a direct calculation.  This act alone is how most people use Cp and Cpk to decieve.  There are many ways to estimate the population standard deviation.  When I see a Cpk reported, if you do not know stability, normality, and how the standard deviation was calculated you should just ignore the value.

    0
    May 7, 2009 at 4:24 pm #183955

    Taylor
    ★ 10 Years ★
    Participant

    Graham
    Your data is simply out of control not sure if its Non-Normal as Mr Haynes said or not, but certainly out of control. All of your points lie below the mean and are trending upward. Review the rules for Control Charts below. At this point given your USL and LSL I would be more inclined to perform a Gage R&R to see if you are even capable of measuring the tolerance within those specifications.
    That said, I have one more question: Are your spec limits true or are they control limits? You may have to recalculate control limits. Also looks like data may be taken at intervals that are too far apart. If this is 30 consecutive parts then check to see if you other underlying issues such as cavity differences, machine fixture differences, etc.
    Out of Control Checks

    One or more points fall outside of the control limits
    When the control chart is divided into zones as if the upper and lower regions in to zones a,b,& c any of the following points are true
            Two points, out of three consecutive points are on the same side of the average in zone a or beyond
            Four points, out of five consecutive points are on the same side of the average in zone b or beyond
            Nine consecutive points are on one side of the average
           There are six consecutive points, increasing or decreasing
           There are 14 consecutive points that alternate up and down
           There are 15 consecutive points within the zone c (above and below the average
    ____________________USL
                  A
    _____________________
                  B
    _____________________
                  C
    ____________________avg
                   C
    ____________________
                     B
    ____________________
                      A
    _____________________LSL
     
    Looking at your data, you violate at least 4 maybe 5 of these rules
     

    0
    May 7, 2009 at 10:57 pm #183961

    Mario Perez-Wilson
    ★ 10 Years ★
    Participant

    Graham,Here is the output of StatXPress for Process Capability using your data. Since the sub-grouping size is unknown to me, I am using sg=1, and the Moving Range chart to test stability. I you have any question let me know.This software answers all the statistical assumptions needed to report process capability or machine capability.Mario Perez-Wilson

    0
    May 27, 2009 at 7:09 pm #184417

    Mike Gizzi
    ★ 10 Years ★
    Participant

    Do you recommend that software?

    0
    May 28, 2009 at 11:15 am #184427

    Severino
    ★ 10 Years ★
    Participant

    I’m pretty sure he would since it appears to be his company…

    0
    May 28, 2009 at 12:07 pm #184428

    Ron
    ★ 10 Years ★
    Member

    The basic problem is the methodology you utilizedto calcualte everything. This is a large issue for those not truly understanding of the mathmatics behind the calculations.
    You did everything correct mathematically but not statistically.
    Process capability looks at the difference from the first reding to the second, from thesecondto third etc. That is why the order of manufacturer is important to maintain.
    Look at your standrard deviation and that calculated by minitab thge two are not the same.
    You used a software to calculate SD by a classical method then plugged it into a standard equation. This does not work in reality.
    When you select a subgroup size of 1 minitab calculate standard deviation using a moving range methodology, and adjacent observations are treated as subgroups.
    Within subgroup capability represents how the proces could perform if all variation were removed
    The Ppk would represent your data

    0
    May 28, 2009 at 2:00 pm #184432

    Mario Perez-Wilson
    ★ 10 Years ★
    Participant

    …but of course. It is exceptional, and yes my company developed it.Mario Perez-Wilson

    0
    May 28, 2009 at 4:21 pm #184438

    Ron
    ★ 10 Years ★
    Member

    This of course is NOT CORRECT.
     
    when you samples sisze are small as they would be in statistical sampling plans using the formal equation for calculating process standard deviation are inaccurate therefore your software is inaccurate.
    you must use an approximation of process standard deviation which is what minitab does.
    Understanding the concepts behind the statistics is important. You can’t just write code and pretend to be an expert.
     

    0
  • Author
    Posts
Viewing 17 posts - 1 through 17 (of 17 total)

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