Sampling Spreadsheet to help determine the Sample Size for Sampling Plans

I have created a "beta version" of an Excel spread sheet to help determine the sample size for sampling plans. I've complained enough about the lousy statistics of most sampling plans, so I figured I ought to try something constructive.

You have to input
* the maximum percentage of defective units that you would like (aka AQL)
* alpha (the odds of rejecting a lot that just barely meets expectations)

* the percentage of defective units that you will NOT accept (let's call it UQL for unacceptable quality level)
* beta (the odds of accepting a lot that fails to meet this minimum standard)

Then you adjust a slider until it tells you you have an acceptable solution (color-coded and everything)! (With a little cleverness, you could work backwards to create c=0 plans or find out what kind of statistics are actually involved with your current plan.)

For example, the default settings as saved are AQL = 1%, UQL =5%, and alpha = beta = 5%.

Within 1-2 minutes, anyone can find that the acceptable plans for these numbers would be

SAMPLE.SIZE....ACCEPT NUMBER
124-137.........2
153-180.........3
199-207.........4

(The reject number is 1 more than the accept number.)

This version is based on the binomial distribution, meaning 1) only 1 defect per unit is possible and 2) the sample size is small compared to the lot size. I could presumably work out similar versions for the Poisson distribution and for the hypergeometric distribution, but I haven't gotten that ambitious yet.

I think the calculations are all correct, but so far there has been no independent confirmation. Use at your own risk!

Any comments/suggested improvements/donations would be appreciated!


Tim F

Nice job...just a couple of nitpicky things.

1. I got several sample sizes to be acceptable with the same Ac and Re numbers. Not sure if you can restrict it to the smallest sample size that is acceptable.

2. Having maximum defect rate (LTPD) and requiring a beta level seems to create the opportunity to have nonstandard sampling plans is possible. Do you have a problem with the standard alpha=0.05 and beta = 0.1 (AQL and LTPD)?

3. Might want to "hide" the calculations for a cleaner appearance.

I like the approach!

Statistical Steven said:

Nice job...just a couple of nitpicky things.

1. I got several sample sizes to be acceptable with the same Ac and Re numbers. Not sure if you can restrict it to the smallest sample size that is acceptable.

Click to expand...

Typically, there will be a few sets of Ac/Re numbers that will work (in the example I gave, these were 2/3, 3/4, and 4/5). Withinin each set, there will be a range of possible sample sizes.

I don't know that I could (within Excel, anyway) restrict the answer to the smallest possible Ac/Re, or the smallest sample size within a given Ac/Re.

I'm also not sure that I would want to. For a beginning user, it would certainly simplify the choice. Sometimes, you might not want the smallest sample size. For example, in my example anything from 124-137 will work for Ac=2/Re=3. A lot size of 124 requires the least testing, but gives the worst possible consumer protection and the best possible producer protection (within the allowed parameters). Going up to 137 gives better consumer protection and poorer producer protection.

Also, you may have several different sampling requirements. By seeing several options, you may find some common element to simplify the proceedure. For example, the parameters listed could have Ac numbers of 2-4. Perhaps another sampling plan could use 4-6 as the the Ac number. Then you could choose sample sizes so that both had Ac=4, so that inspectors would have a simpler procedure to remember.

2. Having maximum defect rate (LTPD) and requiring a beta level seems to create the opportunity to have nonstandard sampling plans is possible. Do you have a problem with the standard alpha=0.05 and beta = 0.1 (AQL and LTPD)?

Click to expand...

I knew ther was a name for max allowable defect rate (Lot Tolerance Percent Defective], but somehow it didn't come to me as I wrote the post. The user is free to set alpha & beta anywhere they want, and if 0.05 and .1 are standard, I could easily set those as the default values.

3. Might want to "hide" the calculations for a cleaner appearance.

Click to expand...

I thought about that, but for the initial version I decided to leave them in, so that people could more easily double-check what I had done and look for problems.
Now that I think about it, I could have put them on the next worksheet so that they can be found, but don't clutter up the main page.

Thanks for the feedback, Steven!

Tim F

Tim Folkerts said:

I have created a "beta version" of an Excel spread sheet to help determine the sample size for sampling plans. I've complained enough about the lousy statistics of most sampling plans, so I figured I ought to try something constructive.

You have to input
* the maximum percentage of defective units that you would like (aka AQL)
* alpha (the odds of rejecting a lot that just barely meets expectations)

* the percentage of defective units that you will NOT accept (let's call it UQL for unacceptable quality level)
* beta (the odds of accepting a lot that fails to meet this minimum standard)

Then you adjust a slider until it tells you you have an acceptable solution (color-coded and everything)! (With a little cleverness, you could work backwards to create c=0 plans or find out what kind of statistics are actually involved with your current plan.)

For example, the default settings as saved are AQL = 1%, UQL =5%, and alpha = beta = 5%.

Within 1-2 minutes, anyone can find that the acceptable plans for these numbers would be

SAMPLE.SIZE....ACCEPT NUMBER
124-137.........2
153-180.........3
199-207.........4

(The reject number is 1 more than the accept number.)

This version is based on the binomial distribution, meaning 1) only 1 defect per unit is possible and 2) the sample size is small compared to the lot size. I could presumably work out similar versions for the Poisson distribution and for the hypergeometric distribution, but I haven't gotten that ambitious yet.

I think the calculations are all correct, but so far there has been no independent confirmation. Use at your own risk!

Any comments/suggested improvements/donations would be appreciated!


Tim F

Click to expand...

The thing I'm not seeing is lot size. How does that figure into things?

JSW05 said:

The thing I'm not seeing is lot size. How does that figure into things?

Click to expand...

Ahhh....the fallacy of MIL-STD-105E. Lot size has nothing to do with the sampling plan. Since the calculations are based on the binomial, the lot size is not used. If you use the hypergeometric, then lot size would be needed. I guess for very small lot sizes the hypergeometric would be appropriate, but hardly used.

The calculation are based on a binomial distribution, which assumes that the sample is small compared to the lot size (say the sample is 10% or less of the total lot). If this is not the case, then the sampling plans listed will be tighter than the alpha & beta that were inputted.

For example, if the spreadsheet says to use Ac=2 for a sample size of 130, but the lot is only 200 total, then you will be much better at accepting good lots and much better at rejecting bad lots than you predicted. If the lot size is 2000, then the predictions should be quite good.

In fact, given the numbers in the example, it would be impossible to reject a good lot for a lot size of 200, since Re=3 and the worst possible good lot would have 1% of 200 = 2 bad pieces. As the lot size increased, the odds of rejecting it would increase toward a maximum of 5%.

The next stage would be to make a new version based on the hypergeometric distribution. This would work better for small lots, where the sample size would likely be a large % of the total lot.

Tim F

P.S. It looks like Statistical Steven was half a step ahead of me in responding about lot size.

Statistical Steven said:

Lot size has nothing to do with the sampling plan. .

Click to expand...

Say what? I must be missing something here. The purpose of sampling is to provide a statistically sound basis for not doing 100% inspection. When it comes to sampling plans in the real world, lot size has everything to do with sample size. The question is, if I have a lot of x number of individuals, and the acceptable defect rate within the lot is y, how many pieces do I need to inspect in order to have a z confidence level that my accept/reject decision will be efficacious?

Tim Folkerts said:

The calculation are based on a binomial distribution, which assumes that the sample is small compared to the lot size (say the sample is 10% or less of the total lot). If this is not the case, then the sampling plans listed will be tighter than the alpha & beta that were inputted.

For example, if the spreadsheet says to use Ac=2 for a sample size of 130, but the lot is only 200 total, then you will be much better at accepting good lots and much better at rejecting bad lots than you predicted. If the lot size is 2000, then the predictions should be quite good.

In fact, given the numbers in the example, it would be impossible to reject a good lot for a lot size of 200, since Re=3 and the worst possible good lot would have 1% of 200 = 2 bad pieces. As the lot size increased, the odds of rejecting it would increase toward a maximum of 5%.

The next stage would be to make a new version based on the hypergeometric distribution. This would work better for small lots, where the sample size would likely be a large % of the total lot.

Tim F

P.S. It looks like Statistical Steven was half a step ahead of me in responding about lot size.

Click to expand...

And I was a half-step ahead of you in my response to Steven. I'm glad we have guys like you and Steven here to provide this sort of resource and be able to explain it patiently to dopes like me .

JSW05 said:

And I was a half-step ahead of you in my response to Steven. I'm glad we have guys like you and Steven here to provide this sort of resource and be able to explain it patiently to dopes like me .

Click to expand...

You are far from a dope...but in fact quite observant. You have to take Tim and my responses in tandem to get the full answer (more of Tim's actually). The implicit assumption of sampling plans is that the sample size is a small percent of the total lot size.

When that is not the case, you use the hypergeometric to get the exact confidence level. The problem is that if your sample size to lot size is large (say more than 10%) then the risk/reward trade-off is minimal.

SPC charting

When setting up SPC system (eg I and MR chart )do we need to check the normality of the data.Since we use the standard deviation (Avg MR for I-MR) in setting up our limits, do we need the data to be normal?