Hi there, i have cpk formula, somehow its not giving me the right value.
Standard CPK formula is Mean - Lower Tolerance Limit / 3 sigma.
can you please check my attachnment, and see whats wrong with my formula. Thanks
my formula in there is
CPK=min(((Nominal+lower Tolerance) - average ) / (3*((max(sample1:sample5)-min(sample1:sample5)/2.326)),(average-(nominal-lower tolerance))/(3*((max(sample1:sample5)-min(sample1:sample5))/2.326)))

please see attachment.

I don't know if i arrange this formula right, cause sometime it gives me differnt value then other software.

Thanks in advance

Hi
So far I can se in the formula I cant find anything wrong except from above specification limit. You add Master value with lower specification limit but in your case it dosent matter cause you have master value in the middle of your range.

The difference between your Excel workbook and other software is how to calculate standarddeviation.
I tried to calculate standarddeviation of your last record and that would give me Cpk of 2,799.

Do you have a big difference between your aplications you should bother else ignore it!

I looked at your data and have a question about the intent before we go into method of calculating Cpk.
I understand that you are trying to calculate Cpk from a sample size of 5. Is that correct? That is too low. Please take a look at confidence interval on Cpk which changes with sample size. The confidence intervals are calculated from Chi square tables.
Sample size---------- Tolerance on Cpk
10 ------------- +/- 45 %
30 ------------- +/- 25 %
100 ------------- +/- 14 %
In view of above, it doesnot serve much purpose to calculate Cpk on small sample size.

Arvind

Agree with Arvind, it's a nonesense to calculate Cpk with such small amount of data. :yuk:

A smaller version of your formula could be:

=IF(MAX(I14:M14)-MIN(I14:M14),MIN((E14+G14)-S14,S14-(E14-G14))/(3*(MAX(I14:M14)-MIN(I14:M14))/2.326),"")

But it's just the same and looks mathematically right, not statistically, look at this forum post attachment: Cpk Confidence.pdf (http://elsmar.com/Forums/attachment.php?attachmentid=1604)

It's posibility that thee other softwares are calculating the Cpk with sample size of 1 (IX -MR chart), individual and moving ranges deal better with such small amount of data.

Please provide a reference for your CpK formula. I have never seen it calculated using the methodology provided.

Thanks
Bill Pflanz

I didn't checked the constants until now and there is something wrong, you used d2=2.326 (for sample size of 5) and you should use A2=0.577

So the formula

Cpk = min( USL - Mean, Mean - LSL) / (3* (Average_Range * A2))

if you take Average_Range as the range => Max - Min

Cpk = min( USL - Mean, Mean - LSL) / (3* (Max()-Min()) * 0.577)

But still, IMO it's a nonesense to make such calculation.:nopity:

Yea thats right my CPK is based on 5 piece study, actually thats what our customers wants, what can we do then. Why some of my CPK valuse are empty, i mean empty cells? Cause its dividing by zero somewhere?
so you think i use 0.577 instead of 2.326?

Thanks for all help.
Thanks

Hi again
Thats because you aint got any difference between your measurements. Have you done a MSA? Check your NDC value. The NDC value should be greater or equal to 5.
NDC (number of distinct categories) which means the discrimination of your gauge.

so you think i use 0.577 instead of 2.326? :bonk:

Check anywhere the formula for Control Limits with subgroup and ranges, they will always use A2 insteed of d2 for the calculation.

UCL = X_Average + A2 * R_Average
LCL = X_Average - A2 * R_Average

Being A2 * R_Average = 3 Ds

So if Cpk = min (Average - LSL, USL - Average) / (3 Ds)

Cpk = min (Average - LSL, USL - Average) / (A2 * R_Average)

A2 for n=5 is 0.577 NOT 2.326 (the d2 value)

If you still want to do it, IMO the best estimate for variation could be using MR_Average insteed of the range.

The estimate of DS = MR_Average / d2 or in your case MR_Average / 1.128

Yea thats right my CPK is based on 5 piece study, actually thats what our customers wants, what can we do then. Why some of my CPK valuse are empty, i mean empty cells? Cause its dividing by zero somewhere?
so you think i use 0.577 instead of 2.326?

Thanks for all help.
Thanks

Based on the information gathered from this discussion thread, what concerns me is that you are calculating a CpK to meet a customer requirement without understanding the theory. You cannot calculate a CpK using 5 samples. The customer is probably referring to sampling in subgroups of 5 and then after collecting data from 20-25 subgroups use that information to estimate a standard deviation. It assumes that you have a stable process.

I noticed you show 17 rows of inspection data and each row has a different specification implying that each row represents a different product. When I suggested collecting 20-25 subgroups, I am talking about one product. If you are making 17 products then you would have to collect 20-25 subgroups of 5 samples for each product. Each would have its own upper and lower specification limits.

Unless I am not interpreting your information correctly, I recommend that you do some more research on CpK calculations before you proceed farther.

Bill Pflanz

Bill :agree1:

Something I get conern too is if the samples where taken with "rational sampling", You can't just take samples (all five) at the same time.

I changed the file to something more nice IMO (Just one Cpk for your process), maybe not right but more appealing for me. I know that theorically a Cpk must be calculed for each product but dealing with 17 products is too much and It looks like you don't have the ability to make a bigger sample, so:

* I calculated the diference with the target for each value
* Calculated the range with the diferences of each row
* So with the enmascarated value (take all at the same scale) obtain the average of the averages and ranges
* With such values I obtained a Cpk ( I obtained Cpk using d2 and A2 just to show both ways to make such calculation)