Elsmar Cove Forum Header Graphic The Elsmar Cove Wiki Sitemap More Free Files The Elsmar Cove Forums Discussion Thread Index Post Attachments Listing Failure Modes Services and Solutions to Problems Elsmar Cove Forums Main Page Elsmar Cove Home Page
NQA-USA
NQA-USA
Miner's MSA (Measurement Systems Analysis) Blog 
Go Back   The Elsmar Cove Forum > Common Quality Assurance Processes and Tools > Inspection, Prints (Drawings), Testing, Sampling and Related Topics > AQL - Acceptable Quality Level
Forum Username

Wooden Line

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

Search the Elsmar Cove
Search Elsmar
Monitor the Elsmar Forum
Follow Marc & Elsmar
Elsmar Cove Forum RSS Feed  Marc Smith's Google+ Page  Marc Smith's Linked In Page   Marc Smith's Elsmar Cove YouTube Page  Marc Smith's Facebook Page  Elsmar Cove Twitter Feed
Elsmar Cove Groups
Elsmar Cove Google+ Group  Elsmar Cove LinkedIn Group  Elsmar Cove Facebook Group
Donate and $ Contributor Forum Access
Courtesy Quick Links

Links that Elsmar Cove visitors will find useful in your quest for knowledge:

Howard's
International Quality Services
Marcelo Antunes'
SQR Consulting
Bob Doering's
Correct SPC - Precision Machining

NIST's Engineering Statistics Handbook
IRCA - International Register of Certified Auditors
SAE - Society of Automotive Engineers
Quality Digest Portal
IEST - Institute of Environmental Sciences and Technology
ASQ - American Society for Quality

Related Topic Tags
aql (acceptable quality level), microsoft excel .xls .xlsx spreadsheet, receiving inspection, sample size, samples and sampling plans, incoming inspection (also see receiving inspection)
Reply
 
Thread Tools Search this Thread Rating: Thread Rating: 2 votes, 4.50 average. Display Modes
  Post Number #1  
Old 18th January 2012, 03:48 PM
IvorFreyrsson

 
 
Total Posts: 3
Please Help! AQL Formula in Excel - Calculating Sample Size based on an AQL Chart

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?

Sponsored Links
  Post Number #2  
Old 18th January 2012, 04:36 PM
Tim Folkerts's Avatar
Tim Folkerts

 
 
Total Posts: 975
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.
Thank You to Tim Folkerts for your informative Post and/or Attachment!
Sponsored Links

  Post Number #3  
Old 18th January 2012, 05:05 PM
NumberCruncher

 
 
Total Posts: 176
Re: AQL Formula in Excel - Calculating Sample Size based on an AQL Chart

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
Thank You to NumberCruncher for your informative Post and/or Attachment!
  Post Number #4  
Old 18th January 2012, 07:13 PM
Statistical Steven's Avatar
Statistical Steven

 
 
Total Posts: 984
Re: AQL Formula in Excel - Calculating Sample Size based on an AQL Chart

Quote:
In Reply to Parent Post by IvorFreyrsson View Post

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?
Thank You to Statistical Steven for your informative Post and/or Attachment!
  Post Number #5  
Old 19th January 2012, 09:59 AM
IvorFreyrsson

 
 
Total Posts: 3
Re: AQL Formula in Excel - Calculating Sample Size based on an AQL Chart

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.
Attached Files: 1. Scan for viruses before using, 2. Please report any 'bad' files by Reporting this post, 3. Use at your Own Risk.
File Type: pdf AQL Plan.pdf (326.4 KB, 534 views)
File Type: xlsm Daily inpection -in process2.xlsm (177.1 KB, 504 views)
  Post Number #6  
Old 19th January 2012, 11:43 AM
NumberCruncher

 
 
Total Posts: 176
Re: AQL Formula in Excel - Calculating Sample Size based on an AQL Chart

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
Attached Files: 1. Scan for viruses before using, 2. Please report any 'bad' files by Reporting this post, 3. Use at your Own Risk.
File Type: xls lookup sheet.xls (14.0 KB, 374 views)
  Post Number #7  
Old 19th January 2012, 01:38 PM
Tim Folkerts's Avatar
Tim Folkerts

 
 
Total Posts: 975
Re: AQL Formula in Excel - Calculating Sample Size based on an AQL Chart

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...TD-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.)
  Post Number #8  
Old 19th January 2012, 02:02 PM
NumberCruncher

 
 
Total Posts: 176
Re: AQL Formula in Excel - Calculating Sample Size based on an AQL Chart

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
Attached Files: 1. Scan for viruses before using, 2. Please report any 'bad' files by Reporting this post, 3. Use at your Own Risk.
File Type: xls lookup sheet v2.xls (7.5 KB, 351 views)
Reply

Lower Navigation Bar
Go Back   The Elsmar Cove Forum > Common Quality Assurance Processes and Tools > Inspection, Prints (Drawings), Testing, Sampling and Related Topics > AQL - Acceptable Quality Level

Do you find this discussion thread helpful and informational?


Bookmarks


Visitors Currently Viewing this Thread: 1 (0 Registered Visitors (Members) and 1 Unregistered Guest Visitors)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Forum Search
Display Modes Rate Thread Content
Rate Thread Content:

Forum Posting Settings
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Discussion Threads
Discussion Thread Title Thread Starter Forum Replies Last Post or Poll Vote
Determining the Total Sample Size using AQL - AQL Lot Quantity question shivan AQL - Acceptable Quality Level 8 24th January 2011 11:44 PM
Calculating Sample Size for very low AQL nate_9 AQL - Acceptable Quality Level 13 8th November 2010 01:24 PM
Calculating Confidence Level and AQL with Increased Sample Size Patzer Inspection, Prints (Drawings), Testing, Sampling and Related Topics 8 23rd July 2010 06:52 AM
Calculating an AQL given a sample size, accept/reject numbers, and lot size seve00 AQL - Acceptable Quality Level 2 10th April 2009 05:19 PM
Software or Excel .xls spreadsheet to calculate sample size and the AQL - ISO 2859-1 Ced - 2003 AQL - Acceptable Quality Level 2 24th September 2002 11:14 AM



The time now is 01:00 PM. All times are GMT -4.
Your time zone can be changed in your UserCP --> Options.


   


Marc Timothy Smith - Elsmar.com
8466 LeSourdsville-West Chester Road, Olde West Chester, Ohio 45069-1929
513 341-6272
NOTE: This forum uses "cookies".