Find the Answers You Seek

Excel tool can help reconcile print-to-part discrepancies

As the quality manager for a manufacturer of custom plastic extrusions, I frequently work with our customers' design engineers to resolve the dimensional discrepancies between their product prints and our prototype samples.

Significant disparities—those affecting the fit or function of the component—are resolved by modifying the tool or process from which the samples were produced. But incidental differences—those with no impact on the component's performance—are often resolved by modifying the product print to match the samples. Microsoft Excel has a powerful tool called "Goal Seek" that makes this resolution a breeze.

New dimension

During a product's development, customers typically order small lots of their new component for various evaluations. From these lots, a technician draws samples from the appropriate subgroups to conduct a dimensional analysis.

When one of these characteristics measures outside the print specification, we calculate its process capability. Then, using Goal Seek, we generate recommendations for a target and tolerance that match the characteristic's natural process variation As a result of these studies, the design engineer can elect to modify the print specification in one of the following ways:

Widen the tolerance, leaving the target the same.

Shift the target, leaving the tolerance the same.

Change the target and tolerance.

Given the dimensional data, a correctly formatted Excel spreadsheet using Goal Seek can derive the values for each of these options.

Putting it into practice

Consider an example in which sigma, potential capability (Cp), upper potential capability (Cpu), lower potential capability (Cpl) and process capability (Cpk) are calculated using the standard formulas, and the desired Cpk is 1.33 (summary statistics and a histogram can be found online).

A cursory review of the data and histogram reveals a few basic observations:

The characteristic displays a low Cpk given the tolerance of plus or minus 0.012.

The curve is shifted positively, straddling the upper specification limit.

The Cp is less than 1.33.

Considering the options for modifying the print specifications to achieve a Cpk of 1.33, our dimension can be resolved by widening the print tolerance or by first shifting the target to the characteristic's mean, and then widening the tolerance. Because the Cp is below 1.33, shifting the target alone will not yield the desired Cpk.

Goal Seek finds the value for an independent variable in one cell so the formula for a dependant variable in another cell returns the desired result. To use Goal Seek in our application, first construct a table where the independent variable is "tolerance range" and the dependant variable of interest is "Cpk." Enter the target, x-bar and sigma values, and the formulas for the dependant variables. Leave the cell for the tolerance range blank.

Then select Tools > Goal Seek from the file menu (in Excel 2007, select the Data tab, and in the Data Tools group, select What-If Analysis > Goal Seek). In the "set cell" box, enter the reference for the cell that contains the formula for the dependant variable (Cpk). In the "to value" box, enter the target Cpk of 1.33. And in the "by changing cell" box, enter the reference for the cell that will contain the independent variable (tolerance range). Goal Seek will then calculate the necessary tolerance range to achieve the target Cpk. In our application, the calculated tolerance range is 0.057.

By rounding up the calculated tolerance range to the next even significant digit and dividing by two, you generate the lowest possible bilateral tolerance that yields the desired Cpk. In our example, the tolerance is plus or minus 0.029.

Product engineers often prefer to minimize the tolerance they give away. In such cases, shifting the target to the process mean before using Goal Seek to calculate the new tolerance can provide a viable solution. Wise engineers will validate any print changes with the appropriate testing. In our example, this technique yields a print specification of 1.358, plus or minus 0.018.

With a little practice, Excel's Goal Seek will prove to be a valuable addition to your quality and reliability toolbox.

Summary statistics in Excel table / Online Figure 1

Histogram derived from summary statistics / Online Figure 2

Using Goal Seek in Excel / Online Figure 3

Ray Harkins is the quality manager of Mercury Plastics Inc. in Middlefield, OH. He earned a bachelor's degree in engineering technology from the University of Akron. Harkins is a senior member of ASQ and a certified quality engineer and calibration technician.