Sampling Spreadsheet to help determine the Sample Size for Sampling Plans

Tim Folkerts

Super Moderator
#1
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
 

Attachments

Elsmar Forum Sponsor

Statistical Steven

Statistician
Staff member
Super Moderator
#2
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!
 

Tim Folkerts

Super Moderator
#3
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.
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)?
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.
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
 

Jim Wynne

Staff member
Admin
#4
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
The thing I'm not seeing is lot size. How does that figure into things?
 

Statistical Steven

Statistician
Staff member
Super Moderator
#5
JSW05 said:
The thing I'm not seeing is lot size. How does that figure into things?
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.
 

Tim Folkerts

Super Moderator
#6
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. :)
 

Jim Wynne

Staff member
Admin
#7
Statistical Steven said:
Lot size has nothing to do with the sampling plan. .
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?
 

Jim Wynne

Staff member
Admin
#8
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. :)
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:agree1: .
 

Statistical Steven

Statistician
Staff member
Super Moderator
#9
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:agree1: .
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.
 
A

Abhijeet

#10
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?
 
Thread starter Similar threads Forum Replies Date
T Double-sided (USL, LSL) Variables sampling plan Z1.9 - Use in Excel spreadsheet? Inspection, Prints (Drawings), Testing, Sampling and Related Topics 2
J IVD sampling sizes for buyer Reliability Analysis - Predictions, Testing and Standards 0
S Which Sampling Plan(s) Should I Use? Inspection, Prints (Drawings), Testing, Sampling and Related Topics 7
C Sampling - ISO 16269-6 vs ISO 2859 Other ISO and International Standards and European Regulations 0
L Sampling Plan Risks AQL - Acceptable Quality Level 6
A Sampling plan for in-process QC (medical devices) Inspection, Prints (Drawings), Testing, Sampling and Related Topics 13
M Minitab Capability of the Population (no sampling) Using Minitab Software 11
W LTPD, AQL, Ppk and Cpk validation sampling plan table Inspection, Prints (Drawings), Testing, Sampling and Related Topics 0
Q Sampling method for Distributor verification activities EU Medical Device Regulations 1
E Change in control plan - Do I have to do sampling? IATF 16949 - Automotive Quality Systems Standard 1
S Is using ANSI/ASQ Z1.4-2008 the correct sampling plan to determine Pass/Fail of Apparel measurements? AQL - Acceptable Quality Level 4
N Sampling Plan for Internal Audits - ISO 2859 or 3951 - Or Neither? Internal Auditing 6
F Product audit sampling plans IATF 16949 - Automotive Quality Systems Standard 3
R Reduced sampling plan for sterial products APQP and PPAP 0
A ISO 2859-1 - 9.3.3.2 Switching score - Sampling Plans and PPM AQL - Acceptable Quality Level 0
R BS EN 12060:1998 - Zinc and zinc alloys Method of sampling Specifications Manufacturing and Related Processes 4
S High voltage testing - ISO 17025 - 7.2.2 Validation of methods and 7.3 Sampling ISO 17025 related Discussions 3
8 Activity Sampling Exercise Lean in Manufacturing and Service Industries 11
T Defining sampling plan for different AQL AQL - Acceptable Quality Level 3
B AS9100D Sampling Plan AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2
R Design verification for interim design outputs - sampling rationale ISO 13485:2016 - Medical Device Quality Management Systems 2
Nicole Desouza Sampling plan for a customer who wants AQL 1.0 (per ANSI Z1.4) AQL - Acceptable Quality Level 5
V How to interpret AQL sampling tables AQL - Acceptable Quality Level 5
A AQL sampling bags of parts Inspection, Prints (Drawings), Testing, Sampling and Related Topics 5
C Applying Weibayes to Attribute sampling Reliability Analysis - Predictions, Testing and Standards 0
I Sampling processes - Who must define the AQL level? AQL - Acceptable Quality Level 9
E Sampling for capability studies for variables and attributes Capability, Accuracy and Stability - Processes, Machines, etc. 4
O Statistical justification of sampling size in V&V tests ISO 13485:2016 - Medical Device Quality Management Systems 5
M Sampling Plan for Alumin High Pressure Die Castings Manufacturing and Related Processes 0
E Sampling plan for orthopedic implant - Process Validation Inspection, Prints (Drawings), Testing, Sampling and Related Topics 4
S Inspection/sampling economics Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
S How to make a Sampling Plan - Conveyor belts Inspection, Prints (Drawings), Testing, Sampling and Related Topics 4
V Statistical basis and justification while comparing / changing sampling plans Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 11
J AQL for Effectiveness Check - What AQL tiers to use on this type of sampling? Nonconformance and Corrective Action 9
N The question asks about sampling plans and AQL Reliability Analysis - Predictions, Testing and Standards 5
S Help develop sampling plan for prototype samples (medical devices) Statistical Analysis Tools, Techniques and SPC 7
A ISO 2859 Single Sampling - Clarification regarding the sampling table Inspection, Prints (Drawings), Testing, Sampling and Related Topics 4
C Are zero defect sampling plans relevant to AS9120B Distributor AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 4
R DCMA Sampling of CSI Parts AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 4
D Sampling method that is representative of the devices supplied by that distributor EU Medical Device Regulations 5
T ISO 13485: 2016 Internal Audit - Is sampling on projects allowed? ISO 13485:2016 - Medical Device Quality Management Systems 6
M Zero Defect Sampling Plan Standards Inspection, Prints (Drawings), Testing, Sampling and Related Topics 4
L ISO 2859-2 LQL sampling plan linked to %confidence/%reliability Inspection, Prints (Drawings), Testing, Sampling and Related Topics 2
K Sampling Plan for transparent tubular plastic parts from 32 different cavities Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
M ISO / ASTM standards for Sampling Plans for Medical Devices Inspection, Prints (Drawings), Testing, Sampling and Related Topics 1
N Sampling for stability studies - Class II surgically invasive devices Other Medical Device and Orthopedic Related Topics 1
S Surveillance Sampling Test - Determining Sample Size Inspection, Prints (Drawings), Testing, Sampling and Related Topics 5
A AQL Sampling Plan SOP / WI - Medical Device Industry Inspection, Prints (Drawings), Testing, Sampling and Related Topics 14
B IATF 16949 Cl. 8.6.6 - Acceptance Criteria - Zero Defects Attribute Data Sampling IATF 16949 - Automotive Quality Systems Standard 2
C Sampling of Class IIb Implantables under EU MDR - Assessments to the EU MDR EU Medical Device Regulations 4

Similar threads

Top Bottom