Seeking: Simple Excel .xls SPC spreadsheet

jager · Oct 16, 2003

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.

Rob Nix · Oct 16, 2003

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.Scott · Oct 20, 2003

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

bpritts · Oct 20, 2003

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

jager · Oct 20, 2003

spc in Excel

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

Rob Nix · Oct 21, 2003

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 · Oct 21, 2003

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:

Rob Nix · Oct 21, 2003

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.

Sam · Oct 21, 2003

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?

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

Darius · Oct 21, 2003

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:

Seeking: Simple Excel .xls SPC spreadsheet

jager

Rob Nix

D.Scott

Attachments

bpritts

Involved - Posts

jager

Rob Nix

Darius

Rob Nix

Sam

Darius

Similar threads