Excel formula for Ppk needed

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.

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? 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

Tim Folkerts said:

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.

Click to expand...

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.

Tim Folkerts said:

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.

Click to expand...

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)