Next Page Previous Page Home Tools & Aids Search Handbook
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?

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]

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:

EXCEL spreadsheet showing the data input
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:

EXCEL screen Goal Seek input menu

Click OK. The screen below will be displayed:

EXCEL screen showing Goal Seek status
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 gamma 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 k2= 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
Home Tools & Aids Search Handbook Previous Page Next Page