Building Confidence


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 2-b. 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. ( c2 = 5.99 for df=2.)

EXCEL allows a different set of x-values for each set of y-values, 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 1-2-3 allow only one set of x-values for plotting, but permit multiple y-values. 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( (xi-L)/S )







(not needed)






Quattro Pro


-0.5 (say)





Lotus 1-2-3


-0.5 (say)





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 a90/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(a90/95 )= POD-1(0.9|L, 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 2-a and 2-b).

With Quattro Pro or Lotus 1-2-3, the process is similar, except the solver finds the min POD (cell U29) for which cell T29, log(a90/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 1-2-3 but the results aren't as easily displayed because of their requirement that only one set of x-values 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]

SElogo.gif (5955 bytes)

[ HOME ] [ Feedback ]

Mail to
Copyright � 1998-2008 Charles Annis, P.E.
Last modified: June 08, 2014