The "Solver" function of Excel can be used to find the values of LL and UL. Solver is found under the "Tools" menu of Excel.
If Solver does not appear in the Tools menu, look for it in the "Add-ins" menu of Tools.
Table 2 shows the means, standard deviations, and minimum and maximum values for hardness measurements of samples of 30 rubber
seals from 21 batches and the 3-sigma acceptance limits.
Table 2. Statistics of Hardness measurements of samples of 30 parts from 21 batches
An Anderson-Darling test indicated that the distribution of all 630 hardness measurements was not significantly different
from a Normal.
For the lower acceptance limit (LL), the target cell for Solver contained:
=1 – (–NORMDIST(LSL, 58.91,1.649,TRUE))^30 and Solver found that LSL = 52.4 made this equal to 0.0013.
For the upper acceptance limit (UL), the target cell for Solver contained:
=1–(1– (1 – NORMDIST (USL, 58.91,1.649,TRUE))) ^30 and Solver found that USL = 65.4, which made this equal to 0.0013.
Thus, for the Hardness measurements, the rules for accepting batches would be as follows: "The mean of 30 parts should be
between 56 and 62 and also the largest value should not be more than 65.4 and the smallest value not less than 52.4."
Acceptance limits for compounds "Distributed like a Poisson or an Exponential"
The residual chemicals in our rubber seals are typically at very low levels, mostly less than the limit of quantification
(LOQ). If the LOQ is 1 µg/g, there will be a large number reported as <1, some reported as 1, a few as 2, and rarely any above
5 or 6. Table 3 contains a typical example. These are the measured concentrations of residual phenanthrene for 443 batches.
Figure 3 shows the histogram.
Table 3. Fitting a Poisson distribution to 443 measurements of Phenanthrene
The histogram suggests that a Poisson distribution could provide a useful description of the frequency distribution and the
values predicted by a Poisson distribution have been added to Figure 3. The predicted Poisson counts indicate that a probabilistic
upper specification limit could be calculated by fitting a Poisson distribution to the data.
The Poisson distribution shows the expected frequencies with which 0, 1, 2, etc. (i.e., rare events) occur in a sample of
fixed time intervals. Since the events are rare, most of the intervals will have no events, some will have 1, a few will have
2 and none will have 5 or 6. Thus, we can use the Poisson distribution by equating the number of µg/g in a sample to the number
of events in a time interval.
Figure 3. Residual phenathrene in 443 batches of seals
The Poisson distribution:
has only one parameter, μ, and that is estimated by the mean of the measurements.
The measurements of concentration in μg/g, are denoted by x, and f(x) is the proportion of the distribution having the value.
X! denotes the product of integers from 1 up to x, for example 3! = 1 * 2 * 3 = 6. The value <1 denotes quantities that are
less than the limit of quantification. We set these to 0.
We can verify if the Poisson distribution is an acceptable fit to our data by calculating and summing quantities called chi-squares.
These are the squared differences between the observed and expected counts adjusted to be comparable by dividing by the expected
Chi-square = (Observed Count – Expected Count)2 / Expected Count
The expected frequency (or count) is calculated by multiplying f(x) by the sample size (Expected = N * f(x)).