Seeking: Simple Excel .xls SPC spreadsheet

J

jager

#1
Does anyone have an Excel based simple SPC sheet that would give Cpk, CP, Ppk, Pp and would allow me to choose the subgroup size? This would be for simple cap studies. It would be nice if it showed a histogram or X-bar & R chart. I can export measurements from the vision system to an Excel sheet easily. I just need to copy the data and paste it to the above. Does anyone have any ideas? Of course, we are on a tight budget and don't want to purchase new full SPC software. Ours is Dos based and is ok except for copy and paste. Thanks.
 
Elsmar Forum Sponsor
R

Rob Nix

#2
You might consider the book "Statistical Quality Control using Excel" by Zimmerman and Icenogle" from ASQ. It is pretty good and comes with a CD with canned routines.
 
D

D.Scott

#3
Jager

Here is an SPC program that I use quite a bit. This zipped file is a working copy of the program but is limited to data inputs of 30 subsets (from 1 to 5 samples) in each file (plenty for any study). If you need more capacity, just open a new file or call Tack and order the program (last I knew it was only about $100). Good luck.

Dave
 

Attachments

bpritts

Involved - Posts
#4
jager,

sounds like you have some good starting points for your search.

One trap to beware. The canned function in Excel for standard deviation (STDEV)
calculates for a population. If you are doing SPC you want to calculate
standard deviation for a sample. Use the STDEVA function instead of STDEV.

(See the microsoft help files on functions.)

In larger samples this doesn't matter much but for small samples it can
screw you up.

Regards

Brad
 
J

jager

#5
spc in Excel

Thanks for the good advice. I've asked my boss about purchasing one of the software packages.
Jager
 
R

Rob Nix

#6
correction

from: Brad
One trap to beware. The canned function in Excel for standard deviation (STDEV)
calculates for a population. If you are doing SPC you want to calculate
standard deviation for a sample. Use the STDEVA function instead of STDEV.
I can't say for sure whether Microsoft has changed formulas for their different versions, but using Excel97 it goes this way: stdev AND stdeva both give you standard deviation based on the sample (denominator = n-1). Stdevp and stdevpa give you the population standard deviation (denominator = n). I tried it long form to test it and it is correct.

The "a" I believe is for the inclusion of attributes, such as text and logical values (TRUE & FALSE = 1 or 0).
 

Darius

Quite Involved in Discussions
#7
Both stdev and stdeva are the same total variation estimate but as Rob said the difference is that one of them is biased and the other unbiased.

Both meassures are NOT for SPC calculations, as Don Wheeler said the variation is estimated with:
* TOTAL VARIATION estimate - the stdev or stdeva of excel
* BETWEEN SAMPLE VARIATION
* WITHIN SAMPLE VARIATION - The best estimate for SPC because the effect of the outliers don't affect too much, the TOTAL VARIATION estimate uses the square of the difference of the value with the mean, so the effect of outliers is bigger, Shewhart (the creator of SPC) recommended in 1930 the use of this estimate for the calculation.

EXCEL doesn't have it self the calculation of the WithinSample variation.

The calculus is easy, uses
For individual:

Moving_Range_Average/d2* AutoCorrelation_Factor

For subgroups

Range_Average /d2/sqrt(Subgroup_Size)

If you are interested on individuals, I did a spreadsheet, it could help you, does you need it for individuals?

:smokin:
 
R

Rob Nix

#8
You are, of course, correct, Darius, that the within sample estimate is a better choice than the calculated sample standard deviation for SPC. Back in my days in production facilities we used that estimate all the time for SPC charts. I was simply clarifying the excel formula differences between sample and population standard deviations. Thanks for your input. :p
 
#9
Darius said:
Both stdev and stdeva are the same total variation estimate but as Rob said the difference is that one of them is biased and the other unbiased.

Both meassures are NOT for SPC calculations, as Don Wheeler said the variation is estimated with:
* TOTAL VARIATION estimate - the stdev or stdeva of excel
* BETWEEN SAMPLE VARIATION
* WITHIN SAMPLE VARIATION - The best estimate for SPC because the effect of the outliers don't affect too much, the TOTAL VARIATION estimate uses the square of the difference of the value with the mean, so the effect of outliers is bigger, Shewhart (the creator of SPC) recommended in 1930 the use of this estimate for the calculation.

EXCEL doesn't have it self the calculation of the WithinSample variation.

The calculus is easy, uses
For individual:

Moving_Range_Average/d2* AutoCorrelation_Factor

For subgroups

Range_Average /d2/sqrt(Subgroup_Size)

If you are interested on individuals, I did a spreadsheet, it could help you, does you need it for individuals?

:smokin:
Darius,
How do these methods correspond with those used in the AIAG SPC manual?
 

Darius

Quite Involved in Discussions
#10
How do these methods correspond with those used in the AIAG SPC manual?

The factor A2 in the R&R analisys is just 3/(d2*sqrt(subgroup_size))

and the factor is multiplied by Range_Average

So for subgroups

WithinSampleVariation = Range_Average /(d2*sqrt(Subgroup_Size))

why 3, because of

Control_Limits = Mean +/- 3 * WithinSampleVariation

I hope, to understand your question

:bigwave:
 
Thread starter Similar threads Forum Replies Date
J Calibration SOP (warning LONG) - Seeking simple procedure General Measurement Device and Calibration Topics 14
D APQP (Advanced Product Quality Planning) - Seeking a simple, effective format Document Control Systems, Procedures, Forms and Templates 6
A ISO 9001 Clause 7.4.1 Supplier Rating System - Seeking simple system example Supplier Quality Assurance and other Supplier Issues 28
W Seeking Guidance Verification Test Strategy for Class B Medical Devices IEC 62304 - Medical Device Software Life Cycle Processes 1
M Seeking advice regarding use of off-the-shelf (OTS) batteries Other Medical Device and Orthopedic Related Topics 4
A ISMS - Seeking VAPT Consultant Food Safety - ISO 22000, HACCP (21 CFR 120) 1
M Medical Device Directive - Seeking common nonconformance write up scenarios CE Marking (Conformité Européene) / CB Scheme 2
silentmonkey Seeking efficient method to manage install base data Manufacturing and Related Processes 0
K Seeking ISO 13485 Registrar Recommendations Registrars and Notified Bodies 15
R Tapping problems - 3/8" hot roll pickled & oiled steel - seeking expertise Manufacturing and Related Processes 15
S Responsibilities between government owner and private subcontractor of a power plant seeking ISO 9001 certification ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 9
B Seeking for an example or a template of a Business Plan Service Industry Specific Topics 3
B Health Canada Recall Definition - Seeking Clarification Canada Medical Device Regulations 5
W Seeking Returned Merchandise Procedure Customer Complaints 1
QMMike New Trend of not seeking Official ISO 9001 Registration ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 33
R Clinical Evaluation according to MEDDEV 2.7.1 (Rev 4) - seeking template ISO 13485:2016 - Medical Device Quality Management Systems 6
T ISO 9001:2015 Training for Management Team in NM - Seeking Suggestions and Providers ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 5
D Seeking Corrective Action Process Examples Nonconformance and Corrective Action 3
M AS9102 Training - Seeking third party agency that provides online AS9102 training AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 1
F Seeking Internship/Co-op Opportunities in Regulatory Affairs/QA/Clinical Domain (USA) Career and Occupation Discussions 4
L Seeking help in Basic Maintenance Knowledge Manufacturing and Related Processes 4
M Seeking opinion for ISO14001 team activity ISO 14001:2015 Specific Discussions 3
smryan Seeking good 2D barcode add-in for Excel Excel .xls Spreadsheet Templates and Tools 6
Sean Kelley Seeking Feedback on ASQ Guide to Failure Mode and Effect Analysis FMEA and Control Plans 2
S Seeking examples of Nonconforming Materials Nonconformance and Corrective Action 5
Sean Kelley Seeking Quality Training for Quality Technicians Training - Internal, External, Online and Distance Learning 1
P Seeking Information on ISO 15504 - Information Technology Process Assessment Other ISO and International Standards and European Regulations 3
K Seeking NDT Training and Certifications Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
Chennaiite Cause Analysis for Verification/Validation Failures - Seeking Opinions Problem Solving, Root Cause Fault and Failure Analysis 3
Q Seeking Examples of current ISO/TS 16949 Training Procedures IATF 16949 - Automotive Quality Systems Standard 2
Ron Rompen Seeking Test Lab which can do 1440 Hour Accelerated Weathering Characteristics Misc. Quality Assurance and Business Systems Related Topics 1
Sean Kelley Seeking Ballooned PFMEA and Control Plans for Training FMEA and Control Plans 4
B Seeking help in answering Australia Tax Withholding Declaration Coffee Break and Water Cooler Discussions 1
H ASQ CQE Exam Review Workshop - Seeking Volunteers! September 2013 ASQ, ANAB, UKAS, IAF, IRCA, Exemplar Global and Related Organizations 7
N Seeking information on use of Byotrol Cleaning Chemicals in Pharma Industry Manufacturing and Related Processes 1
M Seeking input for a process-based quality management system training course ISO 13485:2016 - Medical Device Quality Management Systems 1
B Seeking help in writing marketing message Career and Occupation Discussions 12
B Seeking help in writing an audit report that can stand on the court Internal Auditing 6
S AAMI EC57 for ECG Medical Device - Seeking 3rd Party Test Laboratory US Food and Drug Administration (FDA) 5
W Seeking a Freelance Mechanical Inspector - Source Inspection in Bangalore, India AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 1
V Seeking Internal Audit Plan form or template Internal Auditing 5
W X-ray based NDT Test Equip. Mfger. seeking CE Mark - Must X-Ray Source be included? CE Marking (Conformité Européene) / CB Scheme 5
F Quality Awareness Sessions - Seeking Ideas and Presentation Material Quality Manager and Management Related Issues 4
S Seeking to learn English Video and Suggestions - I am Chinese Coffee Break and Water Cooler Discussions 9
D Seeking material on the Construction Process in IEC 62304 IEC 62304 - Medical Device Software Life Cycle Processes 3
T I'm seeking ISO 13485:2003 Supplier Audit Checklist Supplier Quality Assurance and other Supplier Issues 1
V DIS Inspect software for our CMM Operations - Seeking Contact Information Calibration and Metrology Software and Hardware 3
R "RUO" (Research Use Only) Medical Device now seeking Clinic Use 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 5
R Seeking Medical Device Registration Process in Korea Other Medical Device Regulations World-Wide 4
D Seeking Equipment Verification Standard Procedure books/guide/links Book, Video, Blog and Web Site Reviews and Recommendations 1

Similar threads

Top Bottom