The Elsmar Cove Wiki More Free Files The Elsmar Cove Forums Discussion Thread Index Post Attachments Listing Failure Modes Services and Solutions to Problems Elsmar cove Forums Main Page Elsmar Cove Home Page
Go Back   The Elsmar Cove Forum > Common Quality Assurance Processes and Tools > Quality Tools, Improvement and Analysis > Quality Assurance and Compliance Software Tools / Solutions > Excel .xls Spreadsheet Templates and Tools
Forum Username

Elsmar Cove Forum Visitor Notice(s)


Search the Elsmar Cove
Custom Search
Monitor the Elsmar Forum
Follow Marc & Elsmar
Elsmar Cove Forum RSS Feed  Marc Smith's Google+ Page  Marc Smith's Linked In Page   Marc Smith's Elsmar Cove YouTube Page  Marc Smith's Facebook Page  Elsmar Cove Twitter Feed
Elsmar Cove Groups
Elsmar Cove Google+ Group  Elsmar Cove LinkedIn Group  Elsmar Cove Facebook Group
Sponsor Links





Donate and $ Contributor Forum Access
Courtesy Quick Links

Links that Elsmar Cove visitors will find useful in your quest for knowledge:

Howard's
International Quality Services
Marcelo Antunes'
SQR Consulting
Bob Doering's
Correct SPC - Precision Machining

NIST's Engineering Statistics Handbook
IRCA - International Register of Certified Auditors
SAE - Society of Automotive Engineers
Quality Digest Portal
IEST - Institute of Environmental Sciences and Technology
ASQ - American Society for Quality

Related Topic Tags
microsoft excel .xls .xlsx spreadsheet, forumulas (mathematics), cpk (related to process capability), ppk (related to process performance)
Reply
 
Thread Tools Search this Thread Rating: Thread Rating: 2 votes, 4.00 average. Display Modes
  #1  
Old 19th June 2003, 05:31 AM
lee01 lee01 is offline
Courtesy Access

 
Registration Date: May 2001
Location: Europe and beyond (aka a nomad!)
Age: 36
 
Posts: 89
Thanks Given to Others: 2
Thanked 3 Times in 3 Posts
Karma Power: 61
Karma: 40
lee01 has less than 100 Karma points so far.
Thank You! What is the Formula for Cpk in Excel?

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

Sponsored Links
  #2  
Old 19th June 2003, 10:06 AM
Sam's Avatar
Sam Sam is offline

 
Registration Date: Sep 1999
Location: Oklahoma
Age: 72
 
Posts: 1,447
Thanks Given to Others: 1
Thanked 52 Times in 28 Posts
Karma Power: 206
Karma: 1135
Sam is appreciated, and has over 1100 Karma points.Sam is appreciated, and has over 1100 Karma points.Sam is appreciated, and has over 1100 Karma points.Sam is appreciated, and has over 1100 Karma points.Sam is appreciated, and has over 1100 Karma points.Sam is appreciated, and has over 1100 Karma points.Sam is appreciated, and has over 1100 Karma points.Sam is appreciated, and has over 1100 Karma points.Sam is appreciated, and has over 1100 Karma points.
I don't recognize the equation. How did you get there?

__________________

Sam Goody
Sponsored Links

  #3  
Old 19th June 2003, 10:13 AM
Craig H. Craig H. is offline
Inactive Registered Visitor

 
Registration Date: Jan 2002
Location: Mars
 
Posts: 2,054
Thanks Given to Others: 323
Thanked 312 Times in 241 Posts
Karma Power: 265
Karma: 5274
Craig H. is appreciated, and has over 1700 Karma points.
Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.Craig H. is appreciated, and has over 1700 Karma points.
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?
  #4  
Old 19th June 2003, 11:41 AM
lee01 lee01 is offline
Courtesy Access

 
Registration Date: May 2001
Location: Europe and beyond (aka a nomad!)
Age: 36
 
Posts: 89
Thanks Given to Others: 2
Thanked 3 Times in 3 Posts
Karma Power: 61
Karma: 40
lee01 has less than 100 Karma points so far.
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)
  #5  
Old 19th June 2003, 11:43 AM
lee01 lee01 is offline
Courtesy Access

 
Registration Date: May 2001
Location: Europe and beyond (aka a nomad!)
Age: 36
 
Posts: 89
Thanks Given to Others: 2
Thanked 3 Times in 3 Posts
Karma Power: 61
Karma: 40
lee01 has less than 100 Karma points so far.
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
Attached Files: 1. Scan for viruses before using, 2. Please report any 'bad' files by Reporting this post, 3. Use at your Own Risk.
File Type: doc Cpk Formula in Excel.doc (19.0 KB, 2420 views)
  #6  
Old 19th June 2003, 12:40 PM
Darius's Avatar
Darius Darius is offline
Appreciated Information Resource

 
Registration Date: Mar 2002
Location: Monterrey Mexico
Age: 52
 
Posts: 519
Thanks Given to Others: 68
Thanked 214 Times in 144 Posts
Karma Power: 108
Karma: 3501
Darius is appreciated, and has over 1700 Karma points.
Darius is appreciated, and has over 1700 Karma points.Darius is appreciated, and has over 1700 Karma points.Darius is appreciated, and has over 1700 Karma points.Darius is appreciated, and has over 1700 Karma points.Darius is appreciated, and has over 1700 Karma points.Darius is appreciated, and has over 1700 Karma points.
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)
Thank You to Darius for your informative Post and/or Attachment!
  #7  
Old 19th June 2003, 01:08 PM
Atul Khandekar's Avatar
Atul Khandekar Atul Khandekar is offline
Forum Administrator

 
Registration Date: Mar 2000
Location: Pune, India
Age: 51
 
Posts: 1,336
Thanks Given to Others: 170
Thanked 212 Times in 148 Posts
Karma Power: 400
Atul Khandekar has disabled his/her Karma.
Send a message via Skype™ to Atul Khandekar
Question 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.

Can you please post your original Excel sheet?

__________________

You are never given a wish without also being given the power to make it true. You may have to work for it, however. ~Richard Bach
Thanks to Atul Khandekar for your informative Post and/or Attachment!
  #8  
Old 23rd June 2003, 05:06 AM
lee01 lee01 is offline
Courtesy Access

 
Registration Date: May 2001
Location: Europe and beyond (aka a nomad!)
Age: 36
 
Posts: 89
Thanks Given to Others: 2
Thanked 3 Times in 3 Posts
Karma Power: 61
Karma: 40
lee01 has less than 100 Karma points so far.
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
Reply

Lower Navigation Bar
Go Back   The Elsmar Cove Forum > Common Quality Assurance Processes and Tools > Quality Tools, Improvement and Analysis > Quality Assurance and Compliance Software Tools / Solutions > Excel .xls Spreadsheet Templates and Tools

Do you find this discussion thread helpful and informational?


Bookmarks


Visitors Currently Viewing this Thread: 1 (0 Registered Visitors (Members) and 1 Unregistered Guest Visitors)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Forum Search
Display Modes Rate Thread Content
Rate Thread Content:

Forum Posting Settings
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Discussion Threads
Discussion Thread Title Thread Starter Forum Replies Last Post or Poll Vote
Weibull formula in Excel needs to be verified. vane0326 Excel .xls Spreadsheet Templates and Tools 46 25th June 2011 01:21 PM
Microsoft Excel 2003 Formula Restrictions xtaticblue Excel .xls Spreadsheet Templates and Tools 9 15th April 2011 05:48 AM
Cpk Formula: Am I using it correctly? Excel .xls Spreadsheet form amna77 Excel .xls Spreadsheet Templates and Tools 14 18th September 2009 09:34 AM
Need some help with a formula in Excel Please! Darren Durney Excel .xls Spreadsheet Templates and Tools 11 27th March 2009 06:33 AM
Excel formula for Ppk needed Aitch Excel .xls Spreadsheet Templates and Tools 44 1st July 2006 05:12 AM



The time now is 04:56 PM. All times are GMT -4.
Your time zone can be changed in your UserCP --> Options.


   


Marc Timothy Smith - Elsmar.com
8466 LeSourdsville-West Chester Road, Olde West Chester, Ohio 45069-1929
513 341-6272