7.2.5.4. Two-sided tolerance intervals using EXCEL

7. Product and Process Comparisons
7.2. Comparisons based on data from one process
7.2.5. What intervals contain a fixed percentage of the population values?

7.2.5.4. Two-sided tolerance intervals using EXCEL

Two-sided tolerance intervals using EXCEL

One method for computing factors for two-sided tolerance intervals using EXCEL makes use of the definition

k(2) = r*SQRT((N-1)/Chi-Square(gamma,N-1))

where r is defined by:

p = (1/SQRT(2*PI))*INTEGRAL[(1/SQRT(N))-r to (1/SQRT(N))+r]
[EXP(-t**2/2)]dt

and Chi-Square(gamma,N-1) is the critical value of the chi-square distribution with N - 1 degrees of freedom that is exceeded with probability, gamma .

Interative method

Unfortunately, r can only be found by iteration from the integral above which defines limits within which p percent of the normal distribution lies. An EXCEL calculation is illustrated below for the same problem as on the previous page except where N= 220 measurements are made of thickness. We wish to find tolerance intervals that contain a proportion p = 0.90 of the wafers with probability gamma

= 0.99.

The EXCEL commands for this calculation are shown below. The calculations are approximate and depend on the starting value for r, which is taken to be zero in this example. Calculations should be correct to three signficant digits.

Basic definition of r in EXCEL

Enter 0 in cell A1
Enter 220 (the sample size) in cell B1
Enter in cell C1 the formula:

=NORMDIST((1/SQRT(B1)+A1),0,1,T)-NORMDIST((1/SQRT(B1)-A1),0,1,T)

The screen at this point is:

Iteration step in EXCEL

Click on the green V (not shown here) or press the Enter key. Click on TOOLS and then on GOALSEEK. A drop down menu appears. Then,

Enter C1 (if it is not already there) in the cell in the row labeled: "Set cell:"
Enter 0.9 (which is p) in the cell at the row labelled: "To value:"
Enter A1 in the cell at the row labeled: "By changing cell:"

The screen at this point is:

Click OK. The screen below will be displayed:

Calculation in EXCEL of k factor

Now calculate the k factor from the equation above.

The value r = 1.6484 appears in cell A1
The value N = 220 is in cell B1
Enter which is 0.99 in cell C1
Enter the formula =A1*SQRT((B1-1)/CHIINV(C1,(B1-1))) in cell D1
Press Enter

The screen is:

EXCEL spreadsheet showing the calculation of k

The resulting value k₂= 1.853 appears in cell D1.

Calculation in Dataplot

You can also perform this calculation using the following Dataplot macro.

.  Initialize
let r = 0
let n = 220
let c1 = 1/sqrt(n)
.  Compute R
let function f = norcdf(c+r) - norcdf(c-r) - 0.9
let z = roots f wrt r  for r = -4  4
let r = z(1)
.  Compute K2
let c2 = (n-1)
let k2 = r*sqrt(c2/chsppf(0.01,c2))
. Print results
print "R    = ^r"
print "K2   = ^k2"

Dataplot generates the following output.

 R    = 1.644854
 K2   = 1.849208