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.

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.

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

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

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

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).

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:

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

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?

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:

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

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?

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.

Darius,
So, are you saying that the AIAG SPC manula is inaccurate or incorrect?

: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:

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.