BACK TO BASICS

## How to plot OC curves in Excel

by David Phillips

This article was featured in January 2016’s Best Of Back to Basics edition.

Sampling Plans are valuable in many quality control applications. But it can be difficult to explain the benefits of different plans to colleagues who are unfamiliar with the terminology. It is useful to show operating characteristic (OC) curves to facilitate those discussions. Many sources of OC curve images are inconvenient ("my books are on my desk"), or they are not relevant to the situation at hand (a published curve for a sample size of 70, but not for 68 or 75).

Fortunately, Microsoft Excel includes built-in functions that make drawing OC curves easy. In this column, we’ll focus on attribute (pass/fail) single sampling plans that can be modeled with the binomial distribution, such as ANSI/ASQ Z1.4:2008. Excel can be used for other distributions, such as Poisson and hypergeometric, in a similar manner.

Figure 1 shows an example OC curve. It shows the probability of the sampling plan accepting the lot (on the y axis) based on the actual (and usually unknown) percentage of nonconforming items in the lot (on the x axis). Two values are required to draw the curves: sample size (n) and accept number (c). The accept number is the maximum number of nonconforming units that may be found in the sample for lot acceptance. To put it another way, the lot would be rejected if c + 1 or more samples are nonconforming. In the example spreadsheet in Figure 2, the user would enter values for n and c in cells B2 and B3 respectively.

The x axis lists possible values with the actual percentage of nonconforming parts in the lot. In Figure 2, values for the x axis are in column A, starting in row six. Smaller percentages are closer together to draw a smoother curve.

Y axis values for the OC curve are in column B, starting in row six. These values represent the probability of accepting the lot given n, c and the actual percentage of nonconforming parts (from column A). The probability to accept the lot comes from the binomial distribution. In Excel, the function to calculate this is:

=BINOMDIST(c, n, actual % non-conforming, TRUE)

You can substitute the cell references using cell B6 as an example:

=BINOMDIST(\$B\$3,\$B\$2,(A6/100),TRUE)

Because the values for c and n will always be in cells B3 and B2, \$ symbols are used to fix those cell references so they will not change when cell B6 is copied down to complete the table. The reference for the actual percentage nonconforming (A6) does not need the \$ because it must change for every row (for example, in row seven it needs to be A7 instead of A6).

The percentage nonconforming also must be converted to a proportion (by dividing by 100) for the function to work correctly. Complete the table by copying cell B6 down to match the length of column A. Then, draw the OC curve by creating an xy chart using columns A and B (both starting in row six). Remember that the y axis is shown as a proportion, not a percentage.

OC curves provide a graphical method of comparing various plans in a way that is easy to explain. Excel makes it simple to create custom plans to meet specific requirements. Online Figure 1, for example, compares plans designed to allow lots that are 8% nonconforming to be accepted no more than 10% of the time.

David Phillips is a senior quality engineer at Dentsply Caulk in Milford, DE. A senior member of ASQ, Phillips is a certified quality engineer and a Six Sigma Black Belt. He holds a bachelor’s degree in mechanical engineering from Grove City College in Pennsylvania.

Very good article. Very well explained and useful since Excel is present in our day-by-day.
--Carlos Zoppi, 06-28-2014

Good tool. Thanks!

Some vintage acceptance sampling references that deal with sampling requirements that might be of interest to readers of the David Phillips article set out below. They might facilitate answers to one's daily questions asked of them about particular sampling plans. In particular, Table 1 in Reference 1 would even suggest candidates to meet particular quality requirements. The references are as follows:

1. Kirkpatrick, R. L., Binomial Sampling Plans Indexed By AOL And LTPD, Industrial Quality Control, Vol 22, No 6, December 1965, Pages 290-292
2. W. R. Pabst, D. B. Owen, Harold F Dodge, R. L. Kirkpatrick, Letters To The Editor About Reference 1, Industrial Quality Control, Vol 23, No 1, July 1966, Pages 28-32
3. Grubbs, Frank E. On Designing Single Sampling Inspection Plans, Annals of Math Stat, Vol. X, No 3, 1949, Pages 242-256

The Phillips article of choosing a plan "designed to allow lots that are 8% nonconforming to be accepted no more that 10% of the time" suggested the sampling plan of n = 75 and c = 2. Alternative suggestions from Reference 1, Table 1, are: n = 28 and c =0; n = 48 and c = 1; n = 65 and c = 2. One may not like the c = 0 plan with its severity at the beginning of the OC curve with its very small .20 percent nonconforming necessary to get a 95 percent chance of acceptance. At any rate they would be considerations.

--Robert L Kirkpatrick, 06-10-2014

Out of 2 Ratings