Quality Exclusives

PRE-Control on a Spreadsheet

December 3, 2010
/ Print / Reprints /
ShareMore
/ Text Size+
Figure 1: PRE


PRE-Control is a simple visual control--one that makes the status of an activity obvious--for real time management of production processes. It is especially useful for processes for which no history is available with which to construct a traditional statistical process control (SPC) chart, and it does not rely on any assumptions about the process' probability density function. PRE-Control also works on processes with one-sided specifications whose underlying distributions are often non-normal, i.e. not shaped like a bell curve.

Traditional PRE-Control uses a simple chart with green, yellow and red measurement zones. When it is convenient for the operator or inspector to use a spreadsheet, PRE-Control is easily adaptable to Microsoft Excel or a similar program like Corel Quattro Pro. This article will describe a version that provides immediate visual feedback on each measurement, and it will begin with a basic overview of PRE

PRE-Control Procedure

Unlike the Shewhart control chart, PRE

Figure 2. PRE-Control Switching Rules

The operating rules are then as shown in Figure 2:

To qualify a new production run, the operator must produce five consecutive pieces whose measurements are all in the Green zone. The operator then takes periodic samples of two pieces, and assesses the results as follows:

If both measurements are in the Green zone, or one is Green and one is Yellow, the sample passes and operation continues.

If both measurements are in the Yellow zone in the same direction (high or low), or either is in the Red zone, the operator is to assume that the process has gone out of adjustment, and is not centered on the nominal. The process must then be adjusted to bring it back onto nominal.

After the adjustment is complete, the process must be re-qualified as if it were a new production run.[1] This rule is similar to the switching rules for many acceptance sampling plans, where a failed sample results in tightened sampling.

If the measurements are in the Yellow zone in opposite directions--one high and one low--excessive variation may be responsible.

[1] This is not mentioned explicitly in the reference, but it is reasonable to assume that the operator must confirm the effectiveness of an adjustment by re-qualifying the process.

The reference notes that a disadvantage of PRE

Table 1. Calculation of PRE-Control Zones

Deployment of PRE-Control to a Spreadsheet

The first step is to compute the boundaries of the Green, Yellow and Red zones, as shown in Table 1. Suppose the specification or tolerance limits are [90,110] mils, millimeters, or whatever, and also that the measurements will never be outside the range [80,120]. The latter provision allows error-proofing against misplaced decimal points and similar data entry errors.

Table 2. PRE-Control Spreadsheet Cells

Table 2 shows the layout of the visual control portion of the spreadsheet.

Figure 3. Conditional Formatting of Data Entry Cells

The sample column is for a sample number, date, time, or similar information that is consistent with the work instruction. The Type column must contain a Q or a q (case is apparently irrelevant) for a qualification run of five pieces. This tells the Result column when to tell the operator or inspector whether the sample passes or fails.

The visual control is provided by the conditional formatting of columns C through G as shown in Figure 3, with cell C27 as an example. To access this, select the cell, FORMAT on the menu bar, and "Conditional Formatting." The format may then be copied into all other cells in columns C through G (below row 26 in Table 2).



Condition 1 states that, if the measurement is between the boundaries of the Green zone, the cell background should be green.

Condition 2 states that, if the measurement is outside the Green zone but within the Yellow zone, the cell background should be yellow.

Condition 3 states that, if the measurement is outside the Yellow zone but inside the data limits, the cell background should be red. The test for "inside the data limits" is apparently required to prevent the spreadsheet from interpreting a blank as a zero. An alternative test for a measurement below the LTL might be (C26<=$B$15)*COUNT(C26), since COUNT(C26) will be zero for an empty cell. The test for an entry above the upper data limit may be omitted, in which case a data entry error also will turn the cell red, but the column for "Data Entry" will indicate the reason.

As the operator or inspector enters measurements, the spreadsheet counts them in real time. Examples are for Row 27. The braces "{" and "}" show these to be array summations. To turn a formula into an array summation, press Control-Shift-Enter instead of just Enter.

Green: {=SUM(($C27:$G27>=B$14)*($C27:$G27<=B$13))}

Yellow: {=SUM(($C27:$G27>=B$15)*($C27:$G27
+SUM(($C27:$G27>B$13)*($C27:$G27<=B$12))}

Red: {=SUM(($C27:$G27
+SUM(($C27:$G27>B$12)*ISNUMBER($C27:$G27))}

In the latter case, the ISNUMBER function makes sure a blank will not be treated as a zero. This algorithm will also result in a "FAIL" result if the measurement is outside the data limits, but the error proofing column will indicate the reason. If zero is a valid measurement in the other zones, as it might be if the LTL is negative and the UTL is positive, a similar check for the presence of a number as opposed to a blank will be needed for the green and yellow zones.

The Result column determines whether the sample passes or fails. It begins with the following calculation (with K27 as an example), and returns +1 for a passing result and

Figure 4. Handling of Data Entry Error

Examples of Use

Figure 4 shows the start of a setup or qualification sample of five pieces. The first two measurements are in the Green zone, but the inspector enters 10 instead of 100 for the third. Note that, at this point, the spreadsheet has counted two Green zone entries for the first row.



Figure 5. Successful Qualification or Setup

In Figure 5, the inspector has corrected the third measurement, and then obtained two additional measurements.

Figure 6. Periodic PRE-Control Sampling

Figure 6 shows results for some periodic samples of 2. Note that the spreadsheet determines that sample 4 is a failure immediately, so the inspector does not bother to measure a second piece. At this point, the process requires readjustment.

Figure 7. Unsuccessful and Successful Readjustments and Re-Qualifications

Figure 7 shows an unsuccessful re-qualification followed by a successful one. Note that the spreadsheet determines that the first re-qualification failed upon entry of the first Yellow zone result, so the operator does not make or measure additional pieces before readjustment of the process.

It is possible to develop a chart with Green, Yellow, and Red zones upon which the measurements can be plotted, but there is no need to have a chart when the data table itself provides immediate visual feedback on the status of each measurement.



Table 3. PRE-Control Zones, Zero is Best

Procedure when Zero is Best

For a one-sided specification in which zero is the best possible measurement, the setup is simpler. The Green zone is half the distance from zero to the tolerance limit, the Yellow zone is the remaining distance, and out of tolerance is Red. Table 3 shows an example in which the upper tolerance limit is 100, and the highest conceivable measurement is 140.

Table 4. PRE-Control Calculations, Zero is Best

The data entry portion is similar to Table 2, although the shorter data entry and zone calculation section (Table 3) allows row 22 to be used as the first data entry row. Table 4 shows the calculation portions, with Row 26 as an example. Figure 8 shows the conditional formatting of the measurement cells, with C26 as an example.

Figure 8. Conditional Cell Background Formatting, Zero is Best



Figure 9. PRE-Control Examples, Zero is Best

Condition 1: If the measurement is less than or equal to the Green zone limit, and the cell is not blank, turn the background green.

Condition 2: If the measurement is less than or equal to the Yellow zone limit, and greater than the Green limit, turn the background yellow.

Condition 3: If the measurement is less than or equal to the data limit, and greater than the Yellow zone limit, turn the background red.

Figure 9 shows examples of how the PRE-Control spreadsheet works for the zero is best case.



Table 5. PRE-Control Zones, Minimum is Best

Procedure for One-Sided Tolerance, Minimum or Maximum is Best

For a one-sided specification in which minimum or maximum is best, the Green zone is three quarters of the distance from the best possible measurement to the tolerance limit, the Yellow zone is the remaining distance, and out of tolerance is Red. Table 5 shows an example in which the upper tolerance limit is 100, and the best possible measurement is 40.

Table 6. PRE-Control Calculations, Minimum is Best

The data entry portion is similar to Table 2. Table 6 shows the calculation portions, with Row 26 as an example. Figure 10 shows the conditional formatting of the measurement cells, with C26 as an example.

Figure 8. Conditional Cell Background Formatting, Zero is Best

The data check column tests for values outside the data limits, and also for potential new best measurements; the second part of the formula counts

Figure 10. Conditional Cell Background Formatting, Minimum is Best

Condition 1: If the measurement is less than or equal to the Green zone limit, and greater than or equal to the lower data limit, turn the cell background green.

Condition 2: If the measurement is less than or equal to the Yellow zone limit, and greater than the Green zone limit, turn the cell background yellow.

Condition 3: If the measurement is less than or equal to the upper data limit, and greater than the Yellow zone limit, turn the cell background red.



Figure 11. PRE-Control Examples, Minimum is Best

In the last row, the measurement of 35 is below that of the best known measurement. The Data Entry column alerts the user that a new "best" measurement has been obtained. The work instruction may then say to change the minimum and update the zone limits, to reflect the performance improvement. Note that this is similar to changing the limits of a Shewhart chart to reflect lower variation or some other improvement. It may be desirable to continue on a new worksheet to avoid retroactively changing the zone limits for the previous measurements.

The procedure for "maximum is best" is similar, and Table 7 shows the zone calculations.

Table 7. PRE-Control Zones, Maximum is Best

Table 8 shows the calculations for maximum is best, with row 26 as an example.

Table 8. PRE-Control Calculations, Maximum is Best

-(C26:G26<=$B$8)*(C26:G26>$B$7) returns a -1 if any measurement exceeds that of the best known piece without going over the upper data limit. This tells the user that a new best piece has been produced. Figure 11 shows the conditional cell format.

Figure 12: Conditional Cell Format, Maximum is Best

If the measurement is between the green zone limit and the upper data limit, the cell is green. If it is less than the green zone limit but not the LTL, the cell turns yellow. If it is below the LTL but not the lower data limit (C26>=$B$9 could conceivably be replaced by ISNUMBER(C26) to avoid interpretation of a blank as zero), the cell is red.

Summary

PRE-Control has always been a visual control that requires almost no interpretation by its user. The computer spreadsheet makes it possible to interpret measurements as soon as the user enters them, and to provide immediate feedback on the status of the measurements and the sample. It adds the capacity for real-time detection of data entry errors and, in the case of minimum is best or maximum is best, identification of new best measurements.



References [1] Juran and Gryna, Quality Control Handbook, 4th ed., 24.33 to 24.34

Did you enjoy this article? Click here to subscribe to Quality Magazine. 

You must login or register in order to post a comment.

Multimedia

Videos

Podcasts

 In honor of World Quality Month, we spoke to James Rooney, ASQ Past Chairman of the Board of Directors 2013, for his take on quality around the world.
For more information, read the ASQ Speaking of Quality column.
More Podcasts

Quality Magazine

CoverImage

2014 September

Check out the September 2014 edition of Quality Magazine for features!

Table Of Contents Subscribe

The Skills Gap

What is the key to solving the so-called skills gap in the quality industry?
View Results Poll Archive

Clear Seas Research

qcast_ClearSeas_logo.gifWith access to over one million professionals and more than 60 industry-specific publications,Clear Seas Research offers relevant insights from those who know your industry best. Let us customize a market research solution that exceeds your marketing goals.

eNewsletters

STAY CONNECTED

facebook_40.png twitter_40px.png  youtube_40px.pnglinkedin_40px.png