BACK TO BASICS
Curve Your Enthusiasm
How to plot OC curves in Excel
by David Phillips
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:
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.