Cpk Formula: Am I using it correctly? Excel .xls Spreadsheet form

[amna77]

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

[Narfeldt - 2011]

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!

[Arvind]

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

[Darius]

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

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

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.

[Bill Pflanz]

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

Thanks
Bill Pflanz

[Darius]

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.

[amna77]

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

[Narfeldt - 2011]

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.