In this article, I discuss a simple but useful feature of Excel called the Spin Button, and I use it to illustrate two concepts associated with attribute acceptance sampling plans. The first concept is calculating the probability of lot acceptance, from which the operating characteristic curve of an attribute sampling plan can be drawn. The Spin Button can show visually that the exact probability of lot acceptance determined by the Hypergeometric distribution can be approximated by the Binomial distribution. The second concept is how the Spin Button can be used to design an attribute sampling plan that satisfies certain consumer’s and producer’s risks. You need to have Excel installed on your computer to explore the interactive parts of this article, but the entire article can be read without Excel.

Steve H. K. Ng is a Lecturer in the Department of Information and Communications Technology of the Hong Kong Institute of Vocational Education.

In this article, I discuss a simple but useful feature of Excel called the Spin Button, and I use it to illustrate two concepts associated with attribute acceptance sampling plans. The first concept is calculating the probability of lot acceptance, from which the operating characteristic curve of an attribute sampling plan can be drawn. The Spin Button can show visually that the exact probability of lot acceptance determined by the Hypergeometric distribution can be approximated by the Binomial distribution. The second concept is how the Spin Button can be used to design an attribute sampling plan that satisfies certain consumer’s and producer’s risks. You need to have Excel installed on your computer to explore the interactive parts of this article, but the entire article can be read without Excel.

Steve H. K. Ng is a Lecturer in the Department of Information and Communications Technology of the Hong Kong Institute of Vocational Education.

One of the most widely used quality control tools is the *attribute acceptance sampling plan*, which can be applied in a variety of ways. For example, in the context of manufacturing, it can be used to make sure that the quality of incoming parts satisfies certain requirements before they are assembled, that the quality of semi-finished products is acceptable before they are passed to the next manufacturing stage, or that the quality of finished products satisfies the customer’s specifications before they are shipped.

Each attribute sampling plan has three parameters (*N*, *n*, *c*) -- lot size, sample size, and acceptance number, respectively*.* The operation of an attribute sampling plan is simple. You select a random sample of *n* units from the incoming lot of size *N*. You then determine the number of defective components in the sample. If this number does not exceed the pre-determined *c*, the lot is accepted; otherwise the lot is rejected.

The quality level of a lot is usually expressed as percentage defective or fraction defective. For instance, a quality level of *p * = 10% means that there are 10% defective units in the lot. If *p * = 0%, all units in the lot are good, whereas if *p * = 100%, all units are defective. It is worth noting that, if a lot has a quality level of *p * = 0%, then it will be accepted no matter what sampling plan (*N*, *n*, *c*) is chosen. However, unless the quality level *p* is 100%, a lot with a high percent defective, say 30%, will still have a chance of being accepted.

Despite its wide applicability, there are two types of risk associated with each attribute sampling plan.

- The first type of risk is that a lot with a good quality level is rejected. We want to accept such lots most of the time. That is, the probability
*P*of lot acceptance should be high for good quality level lots. However, because of sampling, there is a possibility that the number of defectives found in the sample exceeds_{a}*c*, which will lead to the decision that a good quality level lot is rejected. This type of risk is called the producer’s risk. - The second type of risk is that a lot with a bad quality level is accepted. We want to reject such lots most of the time. That is, the probability
*P*of lot acceptance should be low for bad quality level lots. However, because of sampling, there is a possibility that the number of defectives found in the sample does not exceed_{a}*c*, which will lead to the decision that a bad quality level lot is accepted. This type of risk is called the consumer’s risk.

The risks associated with a sampling plan can be read from the *operating characteristic* (OC) *curve*, which is a graph showing the probability *P _{a}* of lot acceptance for different lot quality levels.

There are two ways to calculate the probability of lot acceptance. Suppose that the sampling plan is (*N*, *n*, *c*) and the quality level of the lot is *p*. The first method is an exact one. The number *X* of defectives found in a sample will follow a Hypergeometric distribution, so the probability of lot acceptance is

,

Note that *Np* is an integer in this formula.

The second method is approximate. When the ratio is small, the Hypergeometric distribution can be approximated well by the Binomial distribution. Thus the probability of acceptance can be approximated by

.

The following figure shows the OC curves for plans with *N = * 1000, *n * = 50, *c* = 0 and *N* = 500, *n * = 25, *c * = 0. Here the probability of acceptance is calculated using the Hypergeometric distribution.

Using the plan (*N*, *n*, *c*) = (500, 25, 0), a lot with a relatively good quality level of 0.01 will still have about a chance of 0.23 being rejected. That is, the producer’s risk is 0.23. Using the same plan, a lot with a relatively bad quality level of 0.1 will still have a chance of 0.07 being accepted. That is, the consumer’s risk is 0.07. It is worth noting that, despite the two plans (500, 25, 0) and (1000, 50, 0) having the same ratio, their OC curves are different.

The approximation of Hypergeometric distributions by Binomial distributions can be proved mathematically, but one can also observe the concept by using the Spin Button (available in Excel 95 or above), which involves nothing more than "click" and "drag-and-drop". If you need instructions for using this feature, click here.

The following figure shows a spreadsheet in which the Hypergeometric distribution is used to calculate the probability of acceptance for any plan (*N*, *n*, *c*), whereas the Binomial distribution is used for any plan (*n*, *c*), with the assumption that *N* is large enough to justify the approximation. You can **click on the chart** to get to the Excel environment -- also click "NO" to keep the existing information. When you click the Spin Button for *n* or *c*, the two OC curves change accordingly. However, when you click the Spin Button for *N*, only the OC curve plotted with the Hypergeometric probabilities will move.

When *n* or *N* increases, the slopes of the OC curves become steeper gradually. However, the sample size *n* has a much greater effect on the OC curve than the lot size *N*. In fact, when the ratio is small, the lot size *N* has almost no effect on the OC curve. In other words, when the ratio is small, Hypergeometric distributions can well be approximated by Binomial distributions. For this reason, when the lot size *N* is large in comparison with the sample size *n*, the sampling plan (*N*, *n*, *c*) can be replaced by (*n*, *c*) without affecting the OC curve much. The acceptance number *c* has a much greater effect on the OC curve than the sample size *n* and the lot size *N*.

There is a problem with calculating the exact probability *P _{a}* of lot acceptance in Excel because the Hypergeometric distribution does not have a "cumulative" flag. On the other hand, using the Binomial distribution is convenient because it has this flag. Lacking a "cumulative" flag for the Hypergeometric function, I have done something special to handle this situation. For each level of fraction defective from 0.01 to 0.2, I create a row of Hypergeometric probabilities for each

Suppose we want to design a single attribute sampling plan so that

- a good lot with a defective rate of 2% will be accepted 95% of the time , and
- a bad lot with a defective rate of 10% will be accepted 15% of the time .

In other words, we want to find an attribute sampling plan whose *OC* curve passes through the two points (0.02, 0.95) and (0.1, 0.15). How can we design a plan (i.e., choose appropriate values of *n* and *c*) that satisfies these criteria? We suppose that the lot size is large as compared with the sample size, so the Binomial approximation suffices. We could use a mathematical approach (Wetherill, 1969), but we can also make use of the Spin Button to design the desired sampling plan.

Here is the spreadsheet for designing a sampling plan (*n*, *c*) for (0.02, 0.95) and (0.1, 0.15). As before, you can **click on the chart** to get to the Excel environment. Use the Spin Button to change the values of *n* and *c*. You will see that the acceptance number has a much greater effect on the *P _{a}* and hence the shape of the

After experimenting with values of *n* and *c*, you should find that the sampling plan which has an *OC* curve approximately passing through the two points (0.02, 0.95) and (0.1, 0.15) is *n* = 61, *c* = 3.

The Spin Button is a convenient feature for designing single attribute sampling plans. Using the Spin Button is an excellent practical exercise for students who can experiment with data and graphs associated with the data. The Spin Button feature can also be used to study the shape of any mathematical and statistical functions that have one or more parameters.

Wetherill, G. B. (1969). *Sampling Distribution and Quality Control* , Methuen and Co. Ltd.