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.

AuthorPosts

February 12, 2008 at 12:19 pm #49329
Graham LeighParticipant@GrahamLeigh Include @GrahamLeigh in your post and this person will
be notified via email.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.9548.101
47.9512
47.9713
47.970
Mean (A1)
47.976233334
47.986
Standard Error
0.0022482095
47.974
Median
47.9746
47.971
Mode
47.9667
47.966
Standard Deviation (A2)
0.0123139498
47.970
Sample Variance
0.0001516339
47.968
Kurtosis
0.55292437310
47.959
Skewness
0.34370399311
47.968
Range
0.04912
47.977
Minimum
47.95113
47.977
Maximum
4814
47.974
Sum
1439.28715
47.975
Count
3016
47.978
Largest(1)
4817
47.987
Smallest(1)
47.95118
48.000
Confidence Level(95.0%)
0.00459810419
47.991
USL (A3)
48.10020
47.996
LSL (A4)
47.95021
47.966
Cpk
0.71022
47.96323
47.970
Cpk =MIN((A3A1)/(3*A2),(A1A4)/(3*A2))24
47.975
where A1 is the cell that contains the process average25
47.966
A2 is the cell that contains the process standard deviation26
47.992
A3 is the cell that contains the Upper Spec Limit27
47.992
A4 is the cell that contains the Lower Spec Limit.28
47.96629
47.99830
47.9900February 12, 2008 at 1:15 pm #168475You 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
0February 12, 2008 at 1:32 pm #168477On the other hand, after viewing your data in a chart
the diff. in Cpk and Ppk is due to a trend in your data.
Jan0February 12, 2008 at 1:41 pm #168479
Graham LeighParticipant@GrahamLeigh Include @GrahamLeigh in your post and this person will
be notified via email.Thanks Jan
Do you have the correct excel formula please ?
ta
Graham
0February 12, 2008 at 1:54 pm #168481
Graham LeighParticipant@GrahamLeigh Include @GrahamLeigh in your post and this person will
be notified via email.Jan
Thanks again, please tell me correct excel formula for Cpk instead of Ppk
Thanks
Graham
0February 12, 2008 at 2:33 pm #168487
Bower ChielParticipant@BowerChiel Include @BowerChiel in your post and this person will
be notified via email.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(B3B2) 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 Range1
47.951
*2
47.971
=ABS(B3B2)3
47.97
=ABS(B4B3)4
47.986
=ABS(B5B4)5
47.974
=ABS(B6B5)6
47.971
=ABS(B7B6)7
47.966
=ABS(B8B7)8
47.97
=ABS(B9B8)9
47.968
=ABS(B10B9)10
47.959
=ABS(B11B10)11
47.968
=ABS(B12B11)12
47.977
=ABS(B13B12)13
47.977
=ABS(B14B13)14
47.974
=ABS(B15B14)15
47.975
=ABS(B16B15)16
47.978
=ABS(B17B16)17
47.987
=ABS(B18B17)18
48
=ABS(B19B18)19
47.991
=ABS(B20B19)20
47.996
=ABS(B21B20)21
47.966
=ABS(B22B21)22
47.963
=ABS(B23B22)23
47.97
=ABS(B24B23)24
47.975
=ABS(B25B24)25
47.966
=ABS(B26B25)26
47.992
=ABS(B27B26)27
47.992
=ABS(B28B27)28
47.966
=ABS(B29B28)29
47.998
=ABS(B30B29)30
47.99
=ABS(B31B30)Mean Moving Range
=AVERAGE(C3:C31)
Constant d2
1.128
Estimate of sigma
=C32/C33
Best Wishes
Bower Chiel0February 12, 2008 at 2:50 pm #168490
Graham LeighParticipant@GrahamLeigh Include @GrahamLeigh in your post and this person will
be notified via email.Chiel
Thanks but I no understandee. Please could you send excel sheet to [email protected]
Ta
Graham0February 12, 2008 at 9:13 pm #168502The 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
0May 6, 2009 at 8:25 pm #183923your excel result is correct. it will be subgroupsize is 30 in minita, so cpk result is 0.7
0May 7, 2009 at 11:22 am #183936
Rick HaynesMember@RickHaynes Include @RickHaynes in your post and this person will
be notified via email.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 nonnormal 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.0May 7, 2009 at 4:24 pm #183955
TaylorParticipant@ChadVader Include @ChadVader in your post and this person will
be notified via email.Graham
Your data is simply out of control not sure if its NonNormal 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 ChecksOne 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
0May 7, 2009 at 10:57 pm #183961
Mario PerezWilsonParticipant@MarioPerezWilson Include @MarioPerezWilson in your post and this person will
be notified via email.Graham,Here is the output of StatXPress for Process Capability using your data. Since the subgrouping 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 PerezWilson
0May 27, 2009 at 7:09 pm #184417
Mike GizziParticipant@MikeGizzi Include @MikeGizzi in your post and this person will
be notified via email.Do you recommend that software?
0May 28, 2009 at 11:15 am #184427
SeverinoParticipant@Jsev607 Include @Jsev607 in your post and this person will
be notified via email.I’m pretty sure he would since it appears to be his company…
0May 28, 2009 at 12:07 pm #184428The 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 data0May 28, 2009 at 2:00 pm #184432
Mario PerezWilsonParticipant@MarioPerezWilson Include @MarioPerezWilson in your post and this person will
be notified via email.â¦but of course. It is exceptional, and yes my company developed it.Mario PerezWilson
0May 28, 2009 at 4:21 pm #184438This 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 
AuthorPosts
The forum ‘General’ is closed to new topics and replies.