AQL Formula in Excel - Calculating Sample Size based on an AQL Chart

I

IvorFreyrsson

#1
Hi everyone!

This is my first time posting here. I've gotten a lot of great answers from these forums for my job, and since I cannot find an answer after two hours of searching, I figured it was time to make a profile, and get to asking.

I work in Quality Control, specifically Incoming Inspections. I also handle the management of our calibration system.

With that bit of background (it may help) I am posing a question to the larger body of learned minds.

I'm trying to make a formula that will calculate my sample size based off of an AQL standards chart. Our acceptance # is zero, but that won't play a part in my question.

In Excel, let's say I have six cells - A1:A3 and B1:B3

A1:A3 are only text; "AQL Level" (A1), "# of Samples" (A2) and "# Inspected" (A3)

In the other three cells: I have a drop-down in B1, giving the AQL number.
In B2 would go my total number of parts, i.e 450.
In B3, I'd like to develop a formula that will give me the proper # of parts to inspect, based off cells A1 and A2. I am stumped, as I'm not that great with Excel.

I think I'm in the right forum. If not, would you kindly point my noob self in the right direction?
 
Elsmar Forum Sponsor

Tim Folkerts

Super Moderator
#2
Re: AQL Formula in Excel

There are a couple possibilities I can think of, depending on whether you want to look up an answer or to calculate an answer.

To l
ook up an answer. you would have to create some sort of lookup table and basically input the key tables from the standards into your spreadsheet. Then have the spreadsheet look up the answer in the table.

Using the actual Z1.4 tables, actually all you have to do is look up your AQL and find the sampling plan with Ac = 0. The lot size will not matter, since you are forcing the table to select Ac = 0.


To calculate an answer, I created a spreadsheet a few years back that helps determine sample sizes for statistically valid sampling plans.
Sampling Spreadsheet to help determine the Sample Size for Sampling Plans

The spreadsheet is a little more involved that you were thinking. It has places to input AQL, RQL, alpha and beta. Then you can adjust the sample size to find acceptable sampling plans. All of these factors play a role in the statistical calculations. (Note that the lot size does NOT matter to these calculations -- there is more about this in the original thread).

You could also change this process around a bit. You could first set the AQL and alpha and adjust the sample size until Ac = 0. Then you would play around with RQL and beta to make the plan work. For example, you might set beta = 0.1 and then see what RQL that corresponds to.
 
N

NumberCruncher

#3
Hi IvorFreyrsson

Assuming you want to look up the values, you will need to provide a bit more information. Quite simply, there are different possibilities for lookup tables depending on what you want to look up. If you have a single table containing your lookup values, this is simple to do.

If you have several different tables, the lookup will be possible, but more involved.

Can you post the spreadsheet that you already have with some skeleton tables? (This is because I am unfamiliar with incoming inspection sampling, and I can't visualise too clearly what you are trying to look up). You don't need to include the actual "number to be inspected" data, as I suspect this would constitute some form of copyright breach. But if you include just the things you are trying to look up, ie "AQL Level" (A1), "# of Samples" (A2), I should be able to help.

NC
 

Statistical Steven

Statistician
Staff member
Super Moderator
#4
Hi everyone!

This is my first time posting here. I've gotten a lot of great answers from these forums for my job, and since I cannot find an answer after two hours of searching, I figured it was time to make a profile, and get to asking.

I work in Quality Control, specifically Incoming Inspections. I also handle the management of our calibration system.

With that bit of background (it may help) I am posing a question to the larger body of learned minds.

I'm trying to make a formula that will calculate my sample size based off of an AQL standards chart. Our acceptance # is zero, but that won't play a part in my question.

In Excel, let's say I have six cells - A1:A3 and B1:B3

A1:A3 are only text; "AQL Level" (A1), "# of Samples" (A2) and "# Inspected" (A3)

In the other three cells: I have a drop-down in B1, giving the AQL number.
In B2 would go my total number of parts, i.e 450.
In B3, I'd like to develop a formula that will give me the proper # of parts to inspect, based off cells A1 and A2. I am stumped, as I'm not that great with Excel.

I think I'm in the right forum. If not, would you kindly point my noob self in the right direction?
Two solutions:

1. Write a macro that can calculate the number_sample in the following formula HYPGEOM.DIST(0,number_sample,b1*b2,b2,"True") solving it for 0.95.

2. Write a macro that can calculate the number_sample in the following formula BINOM.DIST(0,trials,b1,TRUE) solving it for 0.95.

Having said that, you should have a calculation for LQ in addition to AQL. use the same formulas but solve for a probability of 0.10 instead of 0.95.

Does that make sense?
 
I

IvorFreyrsson

#5
Wow. Thanks guys!

Tim, Your spreadsheet is much more involved than anything we do here. A good tool, but too much for what we need.

NC, I'm including the spreadsheet, and a PDF of the AQL plan we use here. All we'll ever do is use an AQL of 10.0 for standard inspections, 1.0 for tightened (we find more than one defect) inspections, and will default to 100% beyond that. All I'm trying to do is figure out some way to call up the number of samples based on an input of Lot Size.

If I can be pointed in the right direction of just what formulae to use, I can most likely figure out how it's used, and go from there.
 

Attachments

N

NumberCruncher

#6
Hi Ivor

A bit crude but this is what I think you want. You will need to type in the numbers from the aql table yourself (I'm helpful, but not that helpful!)

NC
 

Attachments

Tim Folkerts

Super Moderator
#7
I'm not sure how well you will be able to create a sampling plan based on the criteria you have suggested. You have suggested that you want AQL = 10, and a zero acceptance number (Ac=0). However, as you will find out, these numbers are incompatible with each other in the standards.


I don't have the current Z1.4 handy, but the old MIL-STD 105E should be identical. http://www.everyspec.com/MIL-STD/MIL-STD+(0100+-+0299)/MIL-STD-105E_11675/


For AQL = 10, there is no Ac=0 plan in the standards. The closest they have for AQL = 10 is Ac=1, Re=2 for a sample size of 5! So no matter what the lot size, you would draw 5 samples and reject only if you found 2 defects. According to the standards, you would have to accept lots where the sample had 1 defect out of 5!


There are a couple ways to try to fix this.

1) Drop the Ac=0 requirement. Then you could keep AQL=10, but the sample size would vary from 5 to 125 depending on the lot size. However, the acceptance number would somewhere between 1 and 21!


2) Choose a more stringent AQL, but keep the Ac=0. For example, you could go for AQL = 6.5 with a sample size of 2. Or you could go for AQL = 1 with a sample size of 13. (Note that now the sample size is determined strictly by AQL and lot size will not matter.)
 
N

NumberCruncher

#8
Hi Ivor

Another version. A little simpler from the data entry point of view, you just enter the sample size.

Hopefully it will work. Many of my spreadsheets are created in OpenOffice, then converted to Excel, as was this one.

I would recommend protecting all but the data entry cells.

NC
 

Attachments

I

IvorFreyrsson

#9
NC, Your sheet was very close to what I was looking for. With a little modification, it did the trick.

Thanks, guys! I only hope I can be of as much help to someone else in the future :)
 
Thread starter Similar threads Forum Replies Date
R Formula to tie together - Confidence/Reliability Level AQL? LTPD? Reliability Analysis - Predictions, Testing and Standards 3
A % of defects on the whole batch based on result from inspection under AQL Level II Inspection, Prints (Drawings), Testing, Sampling and Related Topics 6
U How to pick between ANSI Z1.4 vs. ISO2859-1 for AQL Inspection? What are the major differences? AQL - Acceptable Quality Level 4
W LTPD, AQL, Ppk and Cpk validation sampling plan table Inspection, Prints (Drawings), Testing, Sampling and Related Topics 0
R AQL, Consumer Risk and MA Statistical Analysis Tools, Techniques and SPC 2
A AQL - How to count samples with defects for each defect class AQL - Acceptable Quality Level 17
T Defining sampling plan for different AQL AQL - Acceptable Quality Level 3
S ARMY AQL - Requirements which don't have an AQL associated with them Manufacturing and Related Processes 2
M AQL table - I need to sample 1250pcs AQL - Acceptable Quality Level 3
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
I Sampling processes - Who must define the AQL level? AQL - Acceptable Quality Level 9
J AQL for Effectiveness Check - What AQL tiers to use on this type of sampling? Nonconformance and Corrective Action 9
S AQL Inspector's Rule - bring into CQE Exam? Professional Certifications and Degrees 5
N The question asks about sampling plans and AQL Reliability Analysis - Predictions, Testing and Standards 5
K Defining Acceptance Quality Level, I need clarity on AQL 1.5, 2.5, 4.0 AQL - Acceptable Quality Level 5
V Receiving Inspection AQL - Am I allowed to just pick a AQL? Inspection, Prints (Drawings), Testing, Sampling and Related Topics 10
L Established a c=0 "AQL" or "RQL" table AQL - Acceptable Quality Level 5
A AQL Sampling Plan SOP / WI - Medical Device Industry Inspection, Prints (Drawings), Testing, Sampling and Related Topics 14
C Example Work Instruction/Procedure for AQL (Acceptable Quality Level) AQL - Acceptable Quality Level 4
L How to calculate the Probability of Deficiency of the AQL of an Inspection AQL - Acceptable Quality Level 16
P Setting Acceptance Quality Limits (AQL) for New Medical Devices ISO 13485:2016 - Medical Device Quality Management Systems 4
K AQL for Sterile Product - Laminate Tubes 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 5
M How to Mathemetically Derive the AQL Tables given Lot Sizes AQL - Acceptable Quality Level 5
L AQL ANSI z1.4 & z1.9 - What are the differences between these 2 standards? AQL - Acceptable Quality Level 5
P RQL/AQL from Attribute Data Six Sigma 1
B Designing a Sampling Plan - AQL AQL - Acceptable Quality Level 19
S Determining Sample Size - AQL & LQ AQL - Acceptable Quality Level 10
V AQL for Component Level and Assembled Finished Product AQL - Acceptable Quality Level 1
alonFAI AQL and Sampling Size for Components Solderability Test Statistical Analysis Tools, Techniques and SPC 3
A Inspection Plans - How to choose AQL (MIL STD 105E) Excel .xls Spreadsheet Templates and Tools 1
U AQL, Lot Size vs Sampling Plans AQL - Acceptable Quality Level 8
P Which Sampling Tables for AQL? Statistical Analysis Tools, Techniques and SPC 6
K Can I use ANSI/AQL for the Total Number of Containers for the entire Lot? Inspection, Prints (Drawings), Testing, Sampling and Related Topics 1
M Determining c=0 AQL numbers Misc. Quality Assurance and Business Systems Related Topics 3
L Who and when to determine AQL for our product inspection AQL - Acceptable Quality Level 1
K Verification Sampling vs. AQL - Sampl Sizes Inspection, Prints (Drawings), Testing, Sampling and Related Topics 1
E Translating AQL to a Production Line Inspection Plan AQL - Acceptable Quality Level 14
S Class III Sampling Plan - Explain AQL vs. 95%C & 90%R Inspection, Prints (Drawings), Testing, Sampling and Related Topics 1
K Training the absolute beginner to understand AQL Training - Internal, External, Online and Distance Learning 1
S What are typical Acceptable Quality Levels (AQL) adopted in Food industry? Food Safety - ISO 22000, HACCP (21 CFR 120) 1
D What AQL level to use to test LEDs Inspection, Prints (Drawings), Testing, Sampling and Related Topics 5
B AQL Sampling Tables Question Inspection, Prints (Drawings), Testing, Sampling and Related Topics 4
C Constructing an AQL matrix for our QC operators in a flexographic printing outfit AQL - Acceptable Quality Level 4
N Visible particles inspection in powders for injections. Approaches to AQL level Pharmaceuticals (21 CFR Part 210, 21 CFR Part 211 and related Regulations) 1
S Justifying the AQL for a Zero (C=0) Acceptance Plan (Medical Device) AQL - Acceptable Quality Level 6
W Application of AQL (Acceptable Quality Level) Concept AQL - Acceptable Quality Level 15
P Deviating from AQL in a small Retail Apparel Company AQL - Acceptable Quality Level 5
M AQL (Acceptable Quality Level) for Mixed Part Defect IATF 16949 - Automotive Quality Systems Standard 1

Similar threads

Top Bottom