A GENERAL EXCEL SOLUTION FOR LTPD TYPE

SAMPLING PLANS
David C. Trindade, Sun Microsystems, and David J. Meade, AMD
David C. Trindade, Sun Microsystems, 901 San Antonio Road, MS UCUP03-706, Palo Alto, CA 94303

KEY WORDS: Acceptance sampling, sample size, sample is less than 10% of the lot size (Type B
EXCEL add-in, risk probability sampling). In this manner, we can apply the binomial
distribution exactly instead of the more complicated
Abstract hypergeometric distribution (Type A). For a discussion
of the differences between Type A and B sampling see
In this paper we discuss a fairly common problem in lot Duncan [3] or Montgomery [4].
acceptance sampling. Suppose a company qualification
or lot acceptance plan calls for a large sample size n The probability of acceptance of the poorest quality
with a non-zero acceptance number c. For example, a LTPD that can be tolerated in an individual lot is often
plan may call for accepting a lot on three or less failures referred to as the “consumer’s risk.” A value of 10% for
out of 300 sample units. Because the sample units are the consumer’s risk is commonly referenced in LTPD
costly, the manufacturer wants to reduce the acceptance plans. Many product qualification plans are based on
number and consequently the sample size while holding LTPD considerations in order to assure consumer
the rejectable quality percent defective p value constant protection against individual lots of poor quality.
at a specified consumer's risk (e.g., 10% probability of However, a common problem is the need to adjust the
acceptance). Thus, an LTPD sampling plan is desired. sample lot size and corresponding acceptance number
However, typical tables available in the literature cover based on time, money, or resource considerations while
only a limited number of p values for a few risk holding the consumer’s risk constant at a specified
probabilities. For the general situation of any desired p quality level. Because of this objective, many tables
value at any probability of acceptance, what are the are available (see [2], [5] or [6]) to assist in the proper
sample sizes n corresponding to various c values? We selection of sample sizes and acceptance numbers. A
describe methods for obtaining solutions via EXCEL difficulty with utilizing these tables is the restriction to
and present a simple add-in for the calculations. only the values specified in the tables. Other authors
have provided graphical solutions to handle more
general requirements. See Tobias and Trindade [7].
Introduction Further details on LTPD schemes can be found in
Schilling [8]. We present below a general EXCEL
There are several ways to categorize a single-sampling solution that handles any specified LTPD at any
lot acceptance plan for fraction non-conforming probability of acceptance.
product. See discussion in Western Electric handbook
on acceptance sampling [1]. In particular, plans that The following example is based on the problem (solved
have a low probability of acceptance (say, 10%) for a graphically) in Tobias and Trindade [7]
specified quality level are called LTPD or Lot
Tolerance Percent Defective plans. This quality level is Example 1: Consider the case where a given sampling
considered the highest percent defective (that is, the plan calls for a lot to be accepted on three or less
poorest quality) that can be tolerated in a small failures out of 300 sampled units. Because of the cost
percentage of the product. The Dodge-Romig [2] associated with each individual unit, we need to
Sampling Inspection Tables define LTPD as “an construct a sampling plan that will allow the lot to be
allowable percent defective which may be considered rejected on just one failure. One important requirement
as the borderline of distinction between a satisfactory of the new plan is that the LTPD and the probability of
lot and an unsatisfactory one.” An alternate name for acceptance be equivalent to that of the original plan. In
the LTPD is the rejectable quality level or RQL. this example, we will set the probability of acceptance
equal to 10%.
For our purposes, we will consider a random sample of
a lot from a process or from a large lot for which the
Step 1: Begin by creating in a spreadsheet a table like sizes in descending order if a lower acceptance number
the one shown in Figure 1, specifying the sample size n, is desired or in ascending order for a higher c.
the acceptance number c, and the probability of
acceptance (10% for LTPD plans). We are interested in
determining the percent defective at the specified
probability of acceptance, that is, the LTPD.
Step 2: The binomial cumulative distribution function
gives the probability of realizing up to c rejects in a
sample of size n. To determine the percent defective
value that makes the binomial CDF equal to the
probability of acceptance value, we use an identity
between the binomial CDF F(x) and the Beta CDF G(p)
for integer valued shape parameters. (See Bury [9], Figure 3: Illustration of how the Excel worksheet should be
set up prior to solving for the alternative sample size.
page 346.) The relationship is:
The second column should contain the EXCEL
F(x; p, n) = 1 – G(p; x + 1, n – x). BINOMDIST function (binomial CDF) for each sample
size, LTPD, acceptance number combination, as shown
We use the EXCEL BETAINV function to calculate the in Figure 3 (column F). Our goal is to find the smallest
LTPD for the original plan, as illustrated in cell B10. value of n that yields a BINOMDIST function value
The LTPD calculated in this example is 0.02213, as less than or equal to the probability of acceptance, 10%.
shown in Figure 2. The general format of the From Figure 4 we can easily see that the appropriate
BETAINV is sample size is 175. This is the smallest possible sample
size for which the BINOMDIST function is less than or
BETAINV(1-prob of acceptance, c + 1, n – c). equal to 0.10. The format of the BINOMDIST function
is
BINOMDIST(acceptance number, sample
size, LTPD, 1)

The final “1” instructs Excel to calculate the cumulative

distribution function.

Figure 1: Illustration of how the Excel worksheet should be

set up prior to solving for a solution to the original sampling
plan.

Figure 4: Illustration of solving for the correct value of n.

The new sampling plan calls for rejection of any lot

where more than 1 unit fails out of a sample of 175
units. The probability of accepting any given lot at an
Figure 2: Illustration of LTPD calculated for original plan incoming percent defective value equal to the LTPD is
where c=3, n=300, and prob of acceptance = 0.1. 10%.

Step 3: Create a second table with two columns. The

first column should contain a list of possible sample
Automated Analysis Tools

The process of finding an alternative LTPD sampling

plan can be made simple through the use of visual basic
macros. The example below introduces a user-friendly
Excel macro that performs LTPD sampling plan
calculations. We will use the data from the previous
example where c = 3, n = 300, and probability of
acceptance = 10%.

Step 1: Execute the macro and select “Find alternative

Figure 7: Illustration of the alternative LTPD sampling plan
sampling plan.” See Figure 5.
output table. The sample size for the alternative sampling
plan is 175 (reference cell C10).

Program Availability
The EXCEL LTPD add-in, written in Visual Basic by
David Meade, is available for free downloading from
the website www.trindade.com/LTPD.html.
Figure 5: Illustration of LTPD sampling plans dialog box.
References
[1] Western Electric Company (1956), Statistical
Step 2: Enter the information for the original sampling
Quality Control Handbook, Delmar Printing Company,
plan. Then, enter a new acceptance number for the
Charlotte, NC
alternative sampling plan. Finally, enter the worksheet
[2] Dodge, H.F., and H.G. Romig (1959) Sampling
cell address where output table should be placed.
Inspection Tables, Single and Double Sampling, 2nd ed.,
Figure 6 illustrates typical input.
John Wiley and Sons, New York
[3] Duncan, A.J (1986) Quality Control and Industrial
Statistics, 5th ed., Irwin, Homewood, IL
[4] Montgomery, D.C. (1991) Introduction to Statistical
Quality Control, 2nd ed,, John Wiley and Sons, New
York
[5] MIL-STD-105D (1963) Sampling Procedures and
Tables for Inspection by Attributes, U.S. Government
Printing Office
[6] MIL-S-19500G (1963) General Specification for
Semiconductor Devices, U.S. Government Printing
Figure 6: Illustration of alternative sampling plan dialog Office
box. [7] Tobias, P.A. and D.C. Trindade (1995) Applied
Reliability, 2nd ed., Kluwer Academic Publishers,
Step 3: Click the “OK” button. The final analysis Boston, MA
table is show in Figure 7. The routine also provides the [8] Schilling, E.G. (1982) Acceptance Sampling in
approximate AQL to accept 95% of the lots. Quality Control, Marcel Dekker, New York
[9] Bury, K.V. (1975) Statistical Models in Applied
The program also provides the capability for: 1. solving Science, John Wiley and Sons, New York
for the LTPD for a given sampling plan; 2. solving for
the sample size for a specified LTPD and acceptance (Presented August 1999 at the ASA Joint
number. The execution of these routines are self-
evident from the dialog boxes.
Statistical Meetings, Baltimore)