Can someone inform what the Cpk formula I should be using in excel?

I'm currently using:



=IF(B11<B12,B11/3,B12/3)

Converted: If(0.351<2.921,0.351/3,2.921/3)


I'll tell you what, I'm taking a full sample of points and have these values, anyone want to give me an idea how to calculate the value?0.0166
0.0073
0.0003
0.0078
0.0039
0.0138
0.0033
0.0101
0.0008
0.0009
0.0114
0.0129
0.0021
0.0053
0.0173
0.0139
0.0021
0.0007
0.0118
0.0126
0.0019
0
0.0124
0.0123
-0.0036
0.0145
0.0088
0.0101
0.0068
0.0089
0.0178
0.0014
0.0136
0
0.0138
0.0141
0.0023
0.0143
0.0172
0.0038
0.0084
0.0234
0.0129
0
0.0011
0.0119
0.0111
0.0023
0.0039
0.0092
-0.0018
0.0073
0.0014
0.004
0.0116

Mnay many many thanks

Lee01

I don't recognize the equation. How did you get there?

Lee:

Why don't you put the data in an Excel file and attach it to a thread?

I assume the spec is .351 min, 2.921 max?

Sorry my friends I’ve got it know!

I have been creating the following statistics in the following way, please can you have a look at the formula’s to make sure I’m on the correct path.


Cell Statistic & Exactly what I’m
Number Result Entering in Excel


B1 USL =0.005 0.005 (From design)
B2 LSL = -0.005 -0.005 (From design)
B3 STD = 0.001 =STDEVP(B15:B63) (note: B15:B63 is the location of the data)
B4 Mean = 0.001 =AVERAGE(B15:B63)
B5 Min = -0.002 =MIN(B15:B63)
B6 Max = 0.004 =MAX(B15:B63)
B7 Range= 0.006 =MAX(B15:B63)-MIN(B15:B63)
B8 UCL = 0.005 =B4+(3*B3)
B9 LCL = -0.003 =B4-(3*B3)
B10 Cp = 1.25 =(B1-B2)/(6*B3)
B11 Cpu = 3.25 =(B1-B4)/(B3*3)
B12 Cpl = 4.251 =(B4-B2)/(B3*3)
B13 Cpk = 1.08 =MIN(B11:B12)

Sorry my friends I’ve got it know!

I have been creating the following statistics in the following way, please can you have a look at the formula’s to make sure I’m on the correct path.

Its in the attachment

Lee01

Wrong

You are using Total Variation estimate (STDEV) to estimate Control Limits, and you are using Total Variation estimate to calculate Cp, Cpk, Cpu, Cpl.

The index calcualted this way is pp, ppk, ppu, ppl

To calculate the Cp, Cpk, and Control Limits use Moving Range or Averages to estimate the variation.

The Total Variation estimate is affected by the variation between samples and within samples, the estimate for control limits is the within sample variation.

Sigma = MR_average / 1.128 (for individual)
or
Sigma = R_average /d2 for (subgroups <>1)

Lee,

I still don't get it - or maybe I am missing something very obvious!

1.You have posted 55 values, yet the document shows AVERAGE(B15:B63) ie only 48 values.

2. Your computed values are rounded off to 3 decimal digits and the data has 4 decimal digits. There may be some errors due to rounding.

3. I did not try calculting with Excel, but you have so many values above USL (0.005) that Cpk of 1.08 seems unlikely. Then again MIN(3.25,4.251) can't be 1.08 !

4. If by "full sample of points" you mean the entire population, then STDEVP is okay. If not, use STDEV. and yes the index would be Ppk.

Can you please post your original Excel sheet?

Okay! Wait a minute.

I understand where your coming from but. . . . .

I'm trying to work out Cpk, & Cp in excel and I have and willl continue to measure the full spopulation (No sample measurement) so I think I'm using the correct Excel calculation.

Is this worng/ correct what should I do? This is being used and has been used for some time in this multinational organisation so are you sure and how will I inform everyone they have been doing it wrong and how they are doing it wrong and how to to do it correct.

Lee01

Lee,

I'm sure there must be excel spreadsheets for Cp/Cpk posted somewhere on the forum before. You could try to search and look at them.

Alternatively, post your excel file so people can take a look at it and suggest corrections (if any reqd).

Rgds.

Okay here it is you’ve got it!

The question is. . . . .

I’m putting point measurement information into MS Excel. Then I’m trying to compute the relevant statistical calculations using formulas within excel, are these okay, are they the correct formulas to use and if not what should I be doing?????????

Another point, these measurement points are the full quantity of measurement points so I’m making the computations full population accordingly! Is this correct?

Many thanks

Lee01

I'm trying to add the excel file but it will not attach? Any ideas? HELP!

I go through the process but after I complete the process the file is not attached!!!!

Okay here it is you’ve got it!

The question is. . . . .

I’m putting point measurement information into MS Excel. Then I’m trying to compute the relevant statistical calculations using formulas within excel, are these okay, are they the correct formulas to use and if not what should I be doing?????????

Another point, these measurement points are the full quantity of measurement points so I’m making the computations full population accordingly! Is this correct?

Many thanks

Lee01

Lee01,I have a spreadsheet that I put together based on information from the AIAG SPC manual. It's primarily for use with X-bar & R charting but you should be able to revise it to suit your needs.
I plugged in your numbers for reference. Let me know if you would like to review it and I'll send it to your e-mail. Unable to upload a ".xls" file

Unable to upload a ".xls" file
Marc has enabled XLS upload. I think it should be possible now.

Yes please attach the spreadsheet

Man, Many Thanks

Lee01

Atul, Thanks for the note.
As I stated earlier, I developed this spreadsheet from the AIAG SPC manual. It HAs not been checked, by someone else, for accuracy.

Here's what I use for simple stuff. It uses the Z Score to calculate the Cp and Cpk. The formula for sigma can be substituted with population standard deviation for the Pp and Ppk if needed.

Kenny

Here's what I use for simple stuff. It uses the Z Score to calculate the Cp and Cpk. The formula for sigma can be substituted with population standard deviation for the Pp and Ppk if needed.
Kenny

The way the data is presented, we don't know anything about how the data was collected, whether it is time series etc. I think we cannot assume any subgrouping and hence, IMO, we cannot reliably compute Cp,Cpk with RBar/d2 method. We can however calculate Pp,Ppk.

IMO Kenny's method is right - except that STDEV would give you sigma for Pp,Ppk. For Cp,Cpk you would use MRBar/d2.

With Lee's data I get the following results:

No of Points= 55
USL= 0.005
LSL= -0.005
XBar= 0.007763636

Using First principles (n-1)method OR Excel STDEV,
Sigma= 0.006074487
Pp= (USL-LSL)/(6 * Sigma) = 0.274371586
Ppu= (USL-XBar)/(3 * Sigma) = -0.151652658
Ppl= (XBar-LSL)/(3* Sigma) = 0.700395829
Ppk = -0.151652658

Using Excel STDEVP , Sigma= 0.006019011
Pp= 0.276900409
Ppu= -0.153050408
Ppl= 0.706851227
Ppk= -0.153050408

For the sake of calculation, assuming subgroup size=1, we can calculate Cpk :
MRBar= 0.007596296
Sigma = MRBar/1.128 = 0.006734305
Cp= 0.24748903
Cpu= -0.136793936
Cpl= 0.631771996
Cpk= -0.136793936

Okay, I’m using STDEVP and not STDEV because I’m measuring every part and every feature on each part. Not a sample of parts (n-1).

With that I’m calculating the calculations as I would calculate a sample.

I’ll try to put my excel spreadsheet on again but it will not let me and I cannot have a look at other peoples!

Basically, the values are measurement deviations for all hole diameters of a single part. No sample of hole diameters and no sample of produced parts!

I thought Cp & Cpk measures the potential capability of future production runs flling within specification. Whilst Pp & Ppk measures the performance of the measured production. . . Basically?

Please do not complicate things for me? I’m a simple fellow! I had a look at the spreadsheet sent by Sam in it’s a whole lot of numbers and calculations!

My calculations are very simple and to the point!

USL
LSL
STD
Mean
Min
Max
Range
UCL
LCL
CP
Cpu
Cpl
Cpk

Then a list of measurements (in this case hole diameters) and there point Identification (location of the part) A1, A2, A3, and so on . . . . . . .

Simple, to the point, and very easy to understand!

Lee01

Leeo1,
Now that you have determined your method for calculating Cpk, you can concentrate on bringing the process into control. At first glance there appears to be 20 + measurements out of spec. All measurements should be within tolerance prior to calculating Cpk, otherwise the data means nothing.

Okay, I’m using STDEVP and not STDEV because I’m measuring every part and every feature on each part. Not a sample of parts (n-1).

With that I’m calculating the calculations as I would calculate a sample.
...
Basically, the values are measurement deviations for all hole diameters of a single part. No sample of hole diameters and no sample of produced parts!
...


Lee01,

First, I have not looked at your spreadsheet (other than the code samples in this thread). Also, I am not a dimensional measurement guru. However, I think I do see one problem based on what you said (quoted above) and the formulas used earlier in the thread.

As I understand it, you are measuring every the diameter of every hole on every part. (Measured diameter minus nominal diameter, for every hole in the part.) I can agree that you are measuring everything on the drawings and the parts. However, what you have done at that point is produce something else -- a collection of data that is the output of a measurement process. You are then analyzing the output of the measurement process to determine your other statistics.

(Assumptions: your measurment system is regularly calibrated; and that the act of measurement does not cause damage or wear to the measured part.)

A single value from a measurement process is always a random sample from an infinite series of all possible measurements that could be made. This implies that each measurement of the same thing may be different. If you make repeated measurements of the same feature on the same "perfect" part (your "golden master", for example), you will have some variation between measurements and can determine the mean and standard deviation -- which are characteristics of the measuring system. This standard deviation can be used as one of the factors in determining the measurement uncertainty. Since the measurements are by definition random samples, then the appropriate Excel function to use is STDEV().

If you then use the same measurement system to measure the same dimension of the same feature on a series of other parts from a single process, then you can determine the mean and standard deviation of that dimension -- which is an indicator of the process performance. Note that even if you make the measurements on every unit (100% inspection), you still are making measurements which are by definition random samples. Therefore, STDEV() is still the appropriate Excel function to use when calculating the process statistics.

Also, you must evaluate the process statistics in comparison with the uncertainty of the measurement system. Ideally, the measurement system uncertainty will be small enough that it is not a significant part of the measurement. For example, if you are measuring to a resolution of 0.001 then the measurement system uncertainty should be less than 0.00025 or (if economically practical) less than 0.0001.

If you are measuring every dimension of every feature only once on a single part, then you really do not have enough information. Yes, you can run the data through the formulas and create results, but what do they mean? You have a mixture of measurement system uncertainty and production process variation and no way to separate them. This problem repeats with each part.

If you make repeated measurements (generally 3 to 5) of every dimension of every feature on a single part, then you have more information, and can calculate a standard deviation for each dimension. That will give you the range of probable values of that dimension on that part, but you still are not separating the measurement uncertainty and process variation.

If you incorporate something else into the system, then you can separate the measurement uncertainty and the process variation. This "something else" is a check standard -- an artifact of some sort that has representations of dimensions similar to the ones being measured on the production parts, but which have been independently verified by a separate measurement process (preferably one with lower uncertainty.) Then at periodic intervals you add the check standard to the measurement system workload. It gets measured several times, and you use that data to determine the measurement system uncertainty. Once you know that, you can separate it from the routine measurements of production parts, and the process variation is visible. You can then use one control chart to track the production process performance, and another to track the measurement system based on the check standard measurements. An advantage of a control chart for the measurement system is that it can provide evidence that the measurement system may need to be calibrated either more or less frequently.

Lee01,

If you are measuring every dimension of every feature only once on a single part, then you really do not have enough information. Yes, you can run the data through the formulas and create results, but what do they mean? You have a mixture of measurement system uncertainty and production process variation and no way to separate them. This problem repeats with each part.

I would have to disagree with you on this point. If you are advocating multiple measurements on the same feature within the study, you are essentially combining a Gage R&R within your capabilty analysis. Confounding the data in this manner would make it impossible to determine neither the R&R nor the capability indices.

There are however more fundamental questions to be answered before a capability analysis can be embarked upon. Without going into a more complicated than necessary analysis on normality and their effect on the variance calculations (stdev), take a look at a frequency distribution of the data (see attached spreadsheet). This is not "well behaved" data and would be suspect (ie: multiple processes, process adjustments during the sample collection timeframe, different people collecting the data with signifigant differences of technique/training, etc).

Finally, note change to Cpk formula to insure that undefined conditions are not mistakenly reported.

Seeking contemporary thoughts and opinions!

Just wanted to reinforce the idea of getting a visual representation of the data, as WELL as the raw numbers.

Numbers alone can be misleading, resulting in initial assessments of capability (or lack thereof) when an examination of the x&R chart will show such things as bimodality, (potential) special cause, etc.

I played with Excel for a while to get histograms and curves, but then accidentally deleted the spreadsheet with all the work on it :-( Now I either use Minitab (1 copy for the entire plant!), or just do a basic line graph with Excel.