Seeking: Simple Excel .xls SPC spreadsheet

bpritts

Involved - Posts
#11
My apologies to all, and my thanks to Rob, for the catch of my mistake on
stdev vs stdeva vs. stdevp.

The reason we were using std dev. was that we were running x bar/ s
charts instead of x bar/ r charts.

I selected x bar/s based on recommendations that s was a more efficient
(statistical jargon) indicator of variation. This is confirmed in the AIAG
spc manual. In an SPC class long ago, I was told that the best motivation
for the r chart vs. s chart was simplicity in calculation; assuming that the
charting was automated, that s was a better measure. (Shewhart didn't
have Excel!)

I confess that I have never really tested the assumption in real life. It is
usually hard enough just to get accurate, timely data, and none of my
clients ever challenged me on statistical issues.

Has anyone else used the x bar/s instead of x bar r? In particular, did
anyone find that it makes a real world difference?

Regards,

Brad
 
Elsmar Forum Sponsor
#12
Darius said:
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:
I guess I'm a little confused; Within smple variation from the manual is,
Sigma hat=Rbar/d2.
Your equation states that "WithinSampleVariation = Range_Average /(d2*sqrt(Subgroup_Size))" also contains the sqrt of the sample size.
Is this not already included in the calculation of the d2 constant?
 
D

Darius

#13
Brad:
In an SPC class long ago, I was told that the best motivation
for the r chart vs. s chart was simplicity in calculation; assuming that the
charting was automated, that s was a better measure. (Shewhart didn't
have Excel!)
:vfunny:
Simplcity, that's funny, the reason is other, s is a better estimate of the variation when the subgroup size gets larger, in SPC it is an option to calculate with it when subgroup size 5 or larger and mandatory on sample sizes of 10 or larger.

In 1931 "Economic Control of Quality of Manufactures Product" on page 302, Shewhart selected the WithinSubgroup variation as the operational method for use with the control charts, while Shewhart's personal preference was to use the Total Variation, and in 1930 the average mean root square deviation was too easy even with the abscence of calculators.
-------------
Sam:
Sigma hat=Rbar/d2.
Your equation states that "WithinSampleVariation = Range_Average /(d2*sqrt(Subgroup_Size))" also contains the sqrt of the sample size.
Thats right, Sigma hat=Rbar/d2., but with Sigma?, there is to much confusion of different estimates and biased or not biased, literally is a madness.
:bonk:

SD(Average)= SD(X)/SQRT(n)

and the equation for the limits is really:

X_Average_of_Averages +/- 3/(d2* SQRT(n))

:( It's a shame that the term standard deviation has grow an such a great amount of different variation estimates, but what is important is to take the right estimate to the job.

Is this not already included in the calculation of the d2 constant?
NO, it's not, you can calculate the A2 the way I told you and the values will correspond.
 
Last edited by a moderator:
D

Darius

#15
:truce: Nop.., Just as I said:
Thats right, Sigma hat=Rbar/d2., but with Sigma?, there is to much confusion of different estimates... It's a shame that the term standard deviation has grow an such a great amount of different variation estimates, but what is important is to take the right estimate to the job
Sam, sorry about the post, I am sure that I was not clear enought.

The tema of this post was about control limits, with control limits you should use the subgroup square root as a factor to change the statistics that are for individuals to statistics for averages, because you are not representing individual values but averages.

SD(Average)= SD(X)/SQRT(SampleSize)

Sigma hat=Rbar/d2 is right for capability index for example, because you try to determine wich portion of the specs is used by the variation of the individual values.

The problem is that most SPC practitioners use the factor A2 for SPC limits without the knoledge that it includes SubgroupSize^0.5.

So the equation for control limits could be better expressed:
CL = Mean +/- 3* WithinSampleStandarDeviationForIndividuals / sqrt(SampleSize)

--- What I am writing from here is just my way of tinking, don't take it too serious, but the next try to explain other way ----

One interesting point is when you try to obtain, what I call natural limits, There is nothing written, but is easy. Natural Limits is how the limits look like without asuming normal distribution. How I obtain it?, just obtain the standard deviation above the median and below the median (both taken apart), how?, with percentile, taking in account 68 and 95 percentile (and their conterparts in the lower part 32 and 5%) to obtain the variation estimate for each side of the median.

The problem is that the estimates are obtained from the averages, so to obtain a non parametrical index (Cnp or Cnpk) you should multiply the estimate with sqrt(SampleSize) to obtain the standard deviation for individuals.
---------------------------------------------------------------------

Sam, I hope this is more clearer, sorry again, if is still not clear, I hope to explain better next time.
:bonk:
 
C

cleverfox

#16
I am not sure if this will help, but I just use ordinary old excel and have made my control charts from scratch. I can easiliy calculate CPK's or whatever and make changes however I see fit.

Take a look at this, it is a large file, but may be insightful to if you are looking for a home grown version.
 

Attachments

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
normhowe Seeking a De Novo Consultant Paid Consulting, Training and Services 7
H SOP Template for seeking regulatory clearance - MDSAP Document Control Systems, Procedures, Forms and Templates 6
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
S 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
S 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
S 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

Similar threads

Top Bottom