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
• #49329

Graham Leigh
Participant

Hello

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
#168475

Van Loon
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
Jan

0
#168477

Van Loon
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
#168479

Graham Leigh
Participant

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

0
#168481

Graham Leigh
Participant

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

0
#168487

Bower Chiel
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
#168490

Graham Leigh
Participant

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

0
#168502

Ron
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
#183923

mehmet
Participant

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

0
#183936

Rick Haynes
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
#183955

Taylor
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
#183961

Mario Perez-Wilson
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
#184417

Mike Gizzi
Participant

Do you recommend that software?

0
#184427

Severino
Participant

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

0
#184428

Ron
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
#184432

Mario Perez-Wilson
Participant

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

0
#184438

Ron
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
Viewing 17 posts - 1 through 17 (of 17 total)

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