Building the
Spreadsheet, Page 2, Confidence: Building the basic
spreadsheet (Page 1) was easy, and the idea behind Page 2 is equally straightforward even
if its execution is more involved. With Page 1 the solver was used only once  to find the
(L, S) pair which maximizes the lnlikelihood. Since the contour is a locus,
several points on its circumference will need to be estimated. The repetition can be
mitigated using spreadsheet Macro commands. To gain a feel for the task, construct a table
of the model evaluated for several values of POD. This is in the upper left
quadrant of Page 2. The five columns (N ... R) contain x = log(a), POD,
a, L and S. The example spreadsheet uses 17 points from POD =
0.01 to 0.99 chosen to facilitate drawing the POD(a) vs. a curve.
Because the model is evaluated at each point using the same values for (L, S),
these two columns seem unnecessarily redundant. They help, however, in thinking about what
comes next: constructing the right confidence contour.
Consider the plot of the model evaluation, Figure
2b. The right confidence bound can be viewed as either the smallest POD at a
constant cracksize, or the largest cracksize at a constant POD, for which the
likelihood ratio statistic, L
= 5.99. ( c^{2} = 5.99 for df=2.)
EXCEL allows a different set of xvalues for
each set of yvalues, so it is convenient to use the latter definition since the
range of interesting values is always 0.01 to 0.99, whereas the corresponding x
values can vary considerably for differing applications, or even from left to right
confidence bounds for the same application. Quattro Pro and Lotus 123 allow only one set
of xvalues for plotting, but permit multiple yvalues. For these
spreadsheets the first definition (smallest POD at constant cracksize) is more
useful. Either approach provides consistent computation of the POD(a)
confidence bounds.
To construct the right confidence bound, begin by
creating a table similar to the one for the maximum likelihood evaluation. To do this,
simply copy the original table, including titles, to create the new one located at T4.
Change the title to "Right Confidence Contour." Now create an iteration
workspace. For cells T29 through X29 enter the appropriate row from Table 2.
Table 2 (logit link)

Cell S29
(also column M) 
Cell T29
(also column N) 
Cell U29
(also column O) 
Cell V29 
Cell W29 
Cell X29 

exp( (x_{i}L)/S
) 
log(a) 
POD 
row() 
Location 
Scale 
EXCEL 
(not needed) 
=W29+X29*LN(U29/(1U29)) 
0.9 

=$J$6 
=$K$6 
Quattro Pro 
@EXP((T29W29)/X29) 
0.5 (say) 
+S29/(1+S29) 

+$J$6 
+$K$6 
Lotus 123 
@EXP((T29W29)/X29) 
0.5 (say) 
+S29/(1+S29) 

+$J$6 
+$K$6 
I used cell V29 as a
counter in Macros to compute confidence contours. 
Study the entry in cell T29. For EXCEL, it
is the log(cracksize) at a given POD (in cell U29) for the current values of the
model parameters (in cells J6 and K6). Changing the location and scale parameters
necessarily changes T29. We want parameter values which produce the largest log(cracksize)
for which L = criterion.
In other words, the solver will iterate L and S to maximize the objective
function (the contents of cell T29) until the likelihood ratio = 5.99. The resulting L
and S pair represents a point on the confidence contour for the parameter
estimates, and the (T29, U29) pair plots as a point on the right POD bound (see
Figure 2b). This approach actually estimates fiducial limits as was pointed out by a
reviewer. After the solver has converged, the parameter values are copied from cells W29
and X29 and the values are pasted into the table under construction for plotting
later.
As an example, use the solver to find a_{90/95}.
Put 0.9 into cell U29. Invoke the solver, select cell T29, click on max and enter
$J$6:$K$6 as the cells the solver can vary. Enter the constraint as K10=L12, where the
criterion is stored in L12. Finally, click on "solve." The resulting model
parameter estimates produce log(a_{90/95} )= POD^{1}(0.9L,
S) = L + S F^{1}(POD)
which now resides in cell T29. The entire process can be repeated for the next value of POD
until the entire right POD bound and right confidence contour are constructed. The
plotting tool provides graphical summary of the results (Figures 2a and 2b).
With Quattro Pro or Lotus 123, the process is
similar, except the solver finds the min POD (cell U29) for which cell T29,
log(a_{90/95} ), = 0.5 (for example), and the constraint, $K$10=$L$12, is
satisfied. Of course you could still solve for the largest a at constant POD
using Quattro Pro or Lotus 123 but the results aren't as easily displayed because of
their requirement that only one set of xvalues be used for plotting.
This procedure, too, is almost easier to execute than to read and
you can create a macro to automate the entire process. (You'll need two macros  one for
the right confidence contour, and one for the left.) It is informative, however, to
compute a few points on the confidence contour to understand what you're asking the macro
to do. Then record the steps and execute the resulting macro.
[First
page] [back] [next] [Spreadsheets] [References] 