Hello, new here and looking for a ppk formula for Excel.
I've searched and found a wealth of information but fail to find or understand how to formulate the ppk calculation in Excel.
I'll attempt to attach my spread sheet which calculates cpk.
Thank you.

Welcome to the Cove!

I didn't audit the spreadsheet, but it appears that you need to use STDEV instead of STDEVP in row 81. BTW, appearances are that you should have been using that from the start, because the data don't appear to be subgrouped.

Welcome to the Cove!

I didn't audit the spreadsheet, but it appears that you need to use STDEV instead of STDEVP in row 81. BTW, appearances are that you should have been using that from the start, because the data don't appear to be subgrouped.


I am using STDEV to calculate Cpk. I just added line 81 STDEVP when I started trying to formulate ppk and got hung up. The dates represent different manufacturing events.

I am using STDEV to calculate cpk. I just added line 81 STDEVP when I started trying to formulate ppk and got hung up. The dates represent different manufacturing events.

You need to use STDEVP to calculate Cpk and STDEV to calculate Ppk. Have a look at this thread What is the Formula for Cpk in Excel? (http://elsmar.com/Forums/showthread.php?p=52576#post52576) for a related discussion.

I thought I had read just the opposite elsewhere on this thread, but of course I see in the suggested reading that this is not the case.

So what am I getting when I think I am calculating CPK using my formula?

I have spent quite a bit of time searching for a excel formula for ppk and while I find the question asked and lots of discussion, I do not find an example of the ppk formula for excel anywhere.

We are a contract manufacturer and we specialize in short runs. A 250 piece run is big. I know this has it's own set of problems.

Thank you for your time and patience.

In my opinion a Capability Study needs mandatory the histogram in order to ensure that the distribution of points fix with the Normal (Gauss distribution) whether not the cpk or cp are not well defined.

In Delphi was mandatory to receive the capability stadies from suppliers alwasy with the histogram.

DISCLAIMER: This is just my opinion of the way things should be based on my knowledge of statistics, not based on any "official" document or interpretation.

I don't think that the deciding factor for STDEV (sample standard deviation) vs STDEVP (population standard deviation) is Cpk vs Ppk, or subgroups vs individuals. It is (as the name implies) an issue of samples vs entire populations.


"The" standard deviation to characterize a group is STDEVP.

If you draw a sample from a group and calculate STDEVP, you tend to get an answer that is smaller than the STDEVP of the entire group. To fix this, a "fudge factor" is added -- you divide by a slightly smaller number ( (n-1)^0.5 instead of n^0.5 ) which makes the answer bigger by an appropriate amount.

STDEV for a subgroup does a good job of estimating the true STDEVP of the entire group.



If you make 30 parts and use all 30 for a Ppk study, then STDEVP would be appropriate.
If you make 3000 parts and use just 30 for a Ppk study, then STDEV would be appropriate.

IF you make 4 parts at a time and use all 4 as a subgroup, then STDEVP would be appropriate.
IF you make 400 parts at a time and use just 4 as a subgroup, then STDEV would be appropriate.


Practically speaking, it doesn't make a lot of difference, espicially for Ppk. If you have 100 parts in the study, then you will be off by 0.5% if you use the "wrong" standard deviation. Ppk = 1.99 vs Ppk = 2.00 -- big deal.

For Cpk, the difference would be more because the size of the group is smaller -- typically 3-6 in a subgroup. Even here, it ould only be 5-15% difference.


Tim F

DISCLAIMER: This is just my opinion of the way things should be based on my knowledge of statistics, not based on any "official" document or interpretation.

I think it's too bad when a person knowledgeable in statistics has to start a sensible explanation with a disclaimer. The sad fact is that there's a lot about the automotive treatment of statistical analysis that doesn't make any sense, and people do have to be concious of the fact that the way they've been taught to do things doesn't necessarily make sense outside the world of automotive manufacturing.

Practically speaking, it doesn't make a lot of difference, espicially for Ppk. If you have 100 parts in the study, then you will be off by 0.5% if you use the "wrong" standard deviation. Ppk = 1.99 vs Ppk = 2.00 -- big deal.

Very true, although it might make a difference to an anal retentive B3 SQE (or whatever they call them these days) who understands the letter of the SPC manual but is oblivious to its intent. Ppk is, for the most part, a useless statistic, intended, I think, to provide an "out" for instances when the process isn't in statistical control. In point of fact, Cpk isn't a whole lot more useful except as a method of quick comparison; you don't have to be a weatherman to know which way the wind is blowing.

Again, there is a wealth of knowledge here but my initial question remains.
I need to calculate and report a ppk value to my customer who has assigned a specific ppk value to each dimension in the spread sheet.
I just need help formulating this calculation in excel.
I appreciate the help and expertise.

To calculate ppk.

Is the same procedure to calculate Cpk,

The difference between them is that the ppk is applied to the first 100 production parts, That´s why is preliminary.

1) Just take subgroup zise of 5,times 20.

2) Map the 100 point with an histogram.

2.1) If normal distribution -> Goto 3.1

2.2) If not normal -> Goto 3.2

3

3.1

Pp=Tolerance/(6Sigma)
Ppk=(Average-Lower spec limit)/3Sigma
Ppk=Upper specfication limit / 3 sigma

Ppk=Minimum(Ppk,Ppk)




3.2

Pp=Tolerance/(99.865*Percentil-0.13*Percentil)

In my oppinion it exist two possibility to meassure the capability (Long Vs Short term).

Preliminary Study (100 parts)

1) 5 parts -> Pp1, Ppk1 (Normal Distribution)
2) 5 parts -> Pp2, Ppk2 (Normal Distribution)
3) 5 parts -> Pp3, Ppk3 (Normal Distribution)


....

20) 5 parts -> Pp4, Ppk4 (Normal Distribution)


you have 20 pp, ppk therefore you can map a potential preliminary curve haw well is improving your process. the curve tend to be exponential


pp
- -----------------------------------
- -
- -
- -
- -
- -
- - -1-2-3-4-5-6-7-8-9-10-11-12-13-14-14-16----20 (Size)


At this stage the short term ppk, pp is ppk20,pp20 but if you want a long term indez which take into acount all the 100 points as the curve is noraml by the Limit theorema you can add normal distribution and you will have other normal distribution therefore you can calcaulate the pp,ppk with all the 100 part to calculate the pp,ppk aggregated index.

----------------------

You see that the curve tend to a well pp, ppk (Lets say 3) now you can start with Cp, Cpk

1) 5 parts -> cp1, cpk1 (Normal Distribution)
2) 5 parts -> cp2, cpk2 (Normal Distribution)
3) 5 parts -> cp3, cpk3 (Normal Distribution)


....

20) 5 parts -> cp4, cpk4 (Normal Distribution)

How much detail do you need?

Basically:
1) enter the all the data
2) calculate the AVERAGE and STDEV for the data
3) Check which is smaller: (USL-AVERAGE)/(3*STDEV) or (AVERAGE-LSL)/(3*STDEV)

Is there something more specific you need help with? The actual use of Excel? The equations for Ppk?


Tim F

How much detail do you need?

Basically:
1) enter the all the data
2) calculate the AVERAGE and STDEV for the data
3) Check which is smaller: (USL-AVERAGE)/(3*STDEV) or (AVERAGE-LSL)/(3*STDEV)

Is there something more specific you need help with? The actual use of Excel? The equations for Ppk?


Tim F


Yes it is plus Histogram

For Histogram in Excel.

You have to activate the Toolbox first in order to use them this function.

Yes it is plus Histogram

Customer requirements aside, histograms can be useful when there are very large amounts of data, but when the number of individuals is relatively small, and you have the basic statistics at hand, a histogram isn't of much practical use.

Yes but all this Formulas have no sense if the Distribution is not normal. 100 points is not a low number of points is enough, Ford recomend minimum 3 to 5to obtain the sigma and average and therefore the Histogram.



The Histogram according to my criteria is mandatory.

Yes but all this Formulas have no sense if the Distribution is not normal. 100 points is not a low number of points is enough, Ford recomend minimum 3 to 5to obtain the sigma and average and therefore the Histogram.



The Histogram according to my criteria is mandatory.

I don't know whether you're referring to Ford's requirements or your own personal requirements. You'll note that I said, "Customer requirements aside..." meaning that if you have a customer requirement for histograms, then you give them histograms, whether it makes sense or not.

If, on the other hand, you are referring in bold red letters to some personal requirement, I can only say that given 100 data points and the basic statistics, I don't need for someone to draw me a picture. If you do, that's fine. BTW, if you actually do need to see a chart, wouldn't an individuals chart be more useful, especially with regard to time?

Because at the end...

Isn´t it the purpose to obtain ppms. If you not have the Gauss how are you going to calculate?

To be honest I do not understand why Customer ask for Cp, Cpk, Pp, PPk instead of directly in ppm, which must be the useful data for them.

and ppm not depend of the curve, it is always the same ppm.

If someone know please the explanation must be much appreciated.

I don't know whether you're referring to Ford's requirements or your own personal requirements. You'll note that I said, "Customer requirements aside..." meaning that if you have a customer requirement for histograms, then you give them histograms, whether it makes sense or not.

If, on the other hand, you are referring in bold red letters to some personal requirement, I can only say that given 100 data points and the basic statistics, I don't need for someone to draw me a picture. If you do, that's fine. BTW, if you actually do need to see a chart, wouldn't an individuals chart be more useful, especially with regard to time?

------------
Great. I agree.

Great and logical exposition.

Because at the end...

Isn´t it the purpose to obtain ppms. If you not have the Gauss how are you going to calculate?

To be honest I do not understand why Customer ask for Cp, Cpk, Pp, PPk instead of directly in ppm, which must be the useful data for them.

and ppm not depend of the curve, it is always the same ppm.

If someone know please the explanation must be much appreciated.

Statistics such as Cpk are used as tools for prediction. PPM allegedly tells you how many things would be bad if you were to make a million of them, but it really has no predictive value, and makes no sense at all unless you are going to make a million of something. So Cpk and PPM are not directly comparable.

And this thread is straying :topic: .

I have the Ford template to calculate the cp of FTEP Control process course, and in this course the template have first the histogram.

Statistics such as Cpk are used as tools for prediction. PPM allegedly tells you how many things would be bad if you were to make a million of them, but it really has no predictive value, and makes no sense at all unless you are going to make a million of something. So Cpk and PPM are not directly comparable.

And this thread is straying :topic: .


ppms are tools for prediction.

If you produce a million of parts you will know that the (ppm parts) will not meet the characterise you are measuring.

There are matrix that with Cp,Cpk will give you the ppm (It exists a formula), which represent the probability to fail with the variable you are measuring .

Isn´t is the purpose of all this?

If you produce a million of parts you will know that the (ppm parts) will not meet the characterise you are measuring.

There are matrix that with Cp,Cpk will give you the ppm (It exists a formula), which represent the probability to fail with the variable you are measuring .

Isn´t is the purpose of all this?

It's the Cpk analysis (the base statistical analysis) that has the predictive value, not the PPM value that's calculated from it.

I have the Ford template to calculate the cp of FTEP Control process course, and in this course the template have first the histogram.


I attached a photo of it.

It's the Cpk analysis (the base statistical analysis) that has the predictive value, not the PPM value that's calculated from it.


Therefore why not to go right to the end and give predictive ppms?.

for me if someone tell me that their process measuring the diameter is

Cp= 3.4 cpk= 3.5. It is good but how many ppms will be estemated?

I do not know if 0.02ppm or 0.03 ppm.

I would have proffered to receive the final calculation and not to have to do it myself.

Therefore why not to go right to the end and give predictive ppms?.

for me if someone tell me that their process measuring the diameter is

Cp= 3.4 cpk= 3.5. It is good but how many ppms will be estemated?

I do not know if 0.02ppm or 0.03 ppm.

I would have proffered to receive the final calculation and not to have to do it myself.

:truce: I know when I'm licked.

Never lick.

We ALL are in a continous improving process which is Learning?.

Is not this a Quality concept?

;)

Never lick.


I think this is great advice :agree: :agree1:

How much detail do you need?

Basically:
1) enter the all the data
2) calculate the AVERAGE and STDEV for the data
3) Check which is smaller: (USL-AVERAGE)/(3*STDEV) or (AVERAGE-LSL)/(3*STDEV)

Is there something more specific you need help with? The actual use of Excel? The equations for Ppk?


Tim F


I am struggling here, my CMM computer crashed and burned and not much time to study. If you have looked at the Excel sheet I attached, I am calculating average, stdev, cp, k and cpk.
I do have the equasion for ppk but I have tried several different excel formulas and am convinced that I am doing something wrong.
My needs are simple, I just need to calculate ppk using my data in my Excell spread sheet.
After I satisfy my customers requirements, maybe I'll debate cpk vs. ppk, histograms and the lot.
Thanks for your patience.

I'm getting a little more off-topic again, but I wanted to add a couple comments.


Each way to present the data has its advantages and disadvantages. I see each one as being useful in its own circumstances.

Raw data provides the most information. From it, you can make summaries like histograms, counts how many are actually bad, calculations or mean, Cpk, etc. However, it also take the most work on the part of the recipent to interptet the information, since it is just a big collection of numbers.

Defect Rate (aka PPM or percent defective or fraction defective) tells you how many are actually outside the specs, which is quite useful and quite simple. However, it tells nothing about how far out of spec the bad parts are, or how far inside the specs the good parts are.

Capability indices (Cp, Cpk, Pp ...) provide numbers that compare how tightly clustered the parts are relative to the desired specs. Converting to defect rates depends strongly on the actual distribution. Even for distributions that "look" normally distributed, the actual defect rate can to hard to nail down. For distributions that don't look normal, then predictions are almost useless.

Control charts indicate how stable the process is. They provide a look at how the process is changing with time - a feature not available in any of the other forms listed here.

Histograms are great for visual people. They provide at a glance much of the same info available from calculations of defect rates or capability indices, just with less precision. A person with a good sense of numbers could probably make a pretty good estimate of capability and PPM just from a histogram.

And of course, there are many other ways to summarize the data, too!


Decide what information you need to know (or what information is expected by your customer:frust: ) and provide that information using the appropriate tools!


Tim F

OK, after all that philosophy, I actually looked at the data. :notme:

First, I think what you calculate is actually Pp & Ppk, not Cp & Cpk. You are using the standard deviation of all the data pooled together. I redid the calculations the way I would have calculated it, but got the same results as you, so I think you have good numbers for Pp & Ppk.

For Cpk, you would want to group the data into rational subgroups. It looks like you did 8 parts at a shot, so that would seem like a good grouping. You would want to find the STDEV (or perhaps slightly better the STDEVP) for each set of 8 parts. After that, you would need to find a net standard deviation. I don't have a reference handy, but I'm pretty sure you can't just average the results - you need to throw in some "fudge factor" based on the subgroup size.

Since your data seems to be clustered somewhat by these groups, I would expect Cpk to be slightly better than Ppk for your numbers.


Tim F

OK, after all that philosophy, I actually looked at the data. :notme:

First, I think what you calculate is actually Pp & Ppk, not Cp & Cpk. You are using the standard deviation of all the data pooled together. I redid the calculations the way I would have calculated it, but got the same results as you, so I think you have good numbers for Pp & Ppk.

For Cpk, you would want to group the data into rational subgroups. It looks like you did 8 parts at a shot, so that would seem like a good grouping. You would want to find the STDEV (or perhaps slightly better the STDEVP) for each set of 8 parts. After that, you would need to find a net standard deviation. I don't have a reference handy, but I'm pretty sure you can't just average the results - you need to throw in some "fudge factor" based on the subgroup size.

Since your data seems to be clustered somewhat by these groups, I would expect Cpk to be slightly better than Ppk for your numbers.


Tim F


Now that's on target.
I have been using that spread sheet for years and no one has set me straight.
If it is true that I am calculating Pp and Ppk, then I still need help with the Cpk formula.

Hello, new here and looking for a ppk formula for Excel.
I've searched and found a wealth of information but fail to find or understand how to formulate the ppk calculation in Excel.
I'll attempt to attach my spread sheet which calculates cpk.
Thank you.

ei! :bigwave:
Welcome to the cove!

Again, there is a wealth of knowledge here but my initial question remains.
I need to calculate and report a ppk value to my customer who has assigned a specific ppk value to each dimension in the spread sheet.
I just need help formulating this calculation in excel.
I appreciate the help and expertise.

i did a quick fix of your spreadsheet
from what you are basically in need. :read:

you can modify it to what fits your customer
requirement. formula used are for your
reference.

I have spent quite a bit of time searching for a excel formula for ppk and while I find the question asked and lots of discussion, I do not find an example of the ppk formula for excel anywhere.

See below post for additional reference of excel formulas:
X-R chart - I need a Microsoft Excel .xls Programmed X-R chart spreadsheet! (http://elsmar.com/Forums/showpost.php?p=152964&postcount=31)
(Attached spreadsheet w/o protection)

i hope it helps.

Thank you so much for your time and efforts. That is exactly what I was looking for. Actually it is much more than I was looking for. Good stuff!!!
Thanks again.
Aitch

Yes it is plus Histogram


Heir it goes the prrof what I said.

The histogram is mandatory.
See Delphi Capability Sheet.

:bigwave: :bigwave: :bigwave:

Now that's on target.
I have been using that spread sheet for years and no one has set me straight.
If it is true that I am calculating Pp and Ppk, then I still need help with the Cpk formula.


It was not philosophy.

It is important to have the concepts clear.

Heir it goes the prrof what I said.

The histogram is mandatory.
See Delphi Capability Sheet.

:bigwave: :bigwave: :bigwave:



Sorry for the ortography but German, English & Spanish is not a good combination.

:bonk:

Heir it goes the prrof what I said.

The histogram is mandatory.
See Delphi Capability Sheet.

:bigwave: :bigwave: :bigwave:

No need to shout. No one, least of all me, has disputed the point that you keep making, namely that use of histograms might be a customer requirement. Nosotros entendemos:agree1:

ei! :bigwave:
Welcome to the cove!



i did a quick fix of your spreadsheet
from what you are basically in need. :read:

you can modify it to what fits your customer
requirement. formula used are for your
reference.



See below post for additional reference of excel formulas:
X-R chart - I need a Microsoft Excel .xls Programmed X-R chart spreadsheet! (http://elsmar.com/Forums/showpost.php?p=152964&postcount=31)
(Attached spreadsheet w/o protection)

i hope it helps.


I can ensure you that Delphi would reject this study withpout the Histogram

whether not, ask them.

No need to shout. No one, least of all me, has disputed the point that you keep making, namely that use of histograms might be a customer requirement. Nosotros entendemos:agree1:


Great, it is nice to see Spanish.

;)

I can ensure you that Delphi would reject this study withpout the Histogram

whether not, ask them.

:topic: Sí señor, entiendo, if Delphi is your customer.
Pero cada cliente has their own specific requirement. :agree:

i agree with señor Jim
No need to shout. No one, least of all me, has disputed the point that you keep making, namely that use of histograms might be a customer requirement. Nosotros entendemos :agree1:

I see that Aitch (who started this thread)
request is "Excel formula for Ppk needed"
and i assume Aitch already found what
he/she is looking for.

Thank you so much for your time and efforts. That is exactly what I was looking for. Actually it is much more than I was looking for. Good stuff!!!
Thanks again.
Aitch

Thanks also for the previous posts for a very informative post. :thanks:

ei Aitch, :bigwave:
Also, know and understand your customer specific requirement. :read:

I did not know that to write in red and big size was shouting. :(

My apologies. :truce:

It is nice that people know Spanish.

Best Regards.

In order to present you for my "Shouting"

I attached a Delphi useful capability Study.

This one works perfect. :read:

Cheers. :biglaugh:

In order to present you for my "Shouting"

I attached a Delphi useful capability Study.

This one works perfect. :read:

Cheers. :biglaugh:

Thank you very much sir :thanx:

Actually it was my main reference in my SPC.v1.03&v1.04
where i did a huge modifications.

Its part of the Delphi Part Certification.xls file right?

complete set here Partcertall_6.15 3rd ed constants.xls (http://elsmar.com/Forums/showpost.php?p=98579&postcount=11)

Thank you very much sir :thanx:

Actually it was my main reference in my SPC.v1.03&v1.04
where i did a huge modifications.

Its part of the Delphi Part Certification.xls file right?

complete set here Partcertall_6.15 3rd ed constants.xls (http://elsmar.com/Forums/showpost.php?p=98579&postcount=11)


Yes it is, Thanks for you all, for your greart support. too :cool: