# What is the Formula for Cpk in Excel?

L

#### lee01

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

S

#### Sam

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

C

#### Craig H.

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?

L

#### lee01

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)

L

#### lee01

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

D

#### Darius

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)

A

#### Atul Khandekar

Questions...

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.

L

#### lee01

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

A

#### Atul Khandekar

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.

L

#### lee01

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