| Building the Logit
Spreadsheet, Page 1, Parameter Estimates: The
example here uses only a location, L, and scale, S, but the technique is
general and can be used with more than one explanatory variable. Let x=log(cracksize)
be the independent variable, and inspection outcome the dependent variable, with a logit
link. Log10 of cracksize is used to be consistent with NDE engineering
practice.
First, select two cells in which to place initial "guess"
values of the link function parameters L and S. (These are in the upper
right of Page 1 of the sample spreadsheet(), cells J6 and K6.) Use column A for
the specimen number, and enter the data in the next two columns, the first containing ai
and the adjacent column with the experimental outcome, 0 if crack ai was
missed, 1 if it was found. Use the next three columns for xi=log( ai
), (xi-L)/S, and exp( (xi-L)/S
) in columns D, E, and F. Put the link (POD, in this example) in column G. Finally,
make column H the lnlikelihood values (ln of equation 1). Since li = p
when yi = 1, and li = (1-p) when yi
= 0, it is easier to use an IF than to perform the two exponentiations and a
multiplication required by equation 1. The formula for an individual lnlikelihood for row
6 is shown in Table 1 where column G contains POD evaluated using the current
values of L and S and column C holds either a zero or one.
Table 1 (logit link)
| |
column D |
column E |
column F |
column G |
column H |
| |
xi=log(ai) |
(xi-L)/S |
exp( (xi-L)/S
) |
Link |
lnlikelihood |
EXCEL |
=LOG($B4) |
=($D4-$J$6)$K$6 |
=EXP($E6) |
=$F6/(1+$F6) |
=IF($C6,LN($G6,LN(1-$G6)) |
Quattro Pro |
@LOG($B4) |
+($D4-$J$6)$K$6 |
@EXP($E6) |
+$F6/(1+$F6) |
@IF($C6,@LN($G6,@LN(1-$G6)) |
Lotus 1-2-3 |
@LOG($B4) |
+($D4-$J$6)$K$6 |
@EXP($E6) |
+$F6/(1+$F6) |
@IF($C6,@LN($G6,@LN(1-$G6)) |
Spreadsheet users will recognize a dollar
sign preceding a row or column as indicating that the row or column is fixed. Since only
the column letter is preceded by "$", the formula can be copied into new rows
and the row reference will change, but the column reference will not. Row 6, columns D
through H, is then copied into the remaining rows for which the experimental data have
been entered in columns B and C. Finally, define a cell (H64) containing the objective
function to be maximized, SUM(H6 .. H63) in this example. This is the sum of the
lnlikelihood column, and is the spreadsheet equivalent of equation 2. It is important to
compute this sum at the bottom of the lnlikelihood column. We can now use the equation solver to
iterate L and S values to maximize the objective function, that is, to find
the maximum likelihood. (Later, the solver will be used to find values for L and S
for which the likelihood ratio statistic, L,
meets some appropriate criterion, e.g.: c2.) To invoke the solver, in EXCEL click on FORMULA, SOLVER; in Quattro Pro, click
on TOOLS, OPTIMIZER; in Lotus 1-2-3, click on RANGE, ANALYZE, SOLVER.
Then enter the cell address containing the sum of the
lnlikelihood $H$5, and choose MAX. Enter the cell addresses for the location and scale, L
and S, $J$6 and $K$6, after having placed beginning "guesses" in these
cells. If your solver allows it, select automatic scaling, quadratic estimates, central
derivatives, and Newton's method().
The process is illustrated by the sample spreadsheet Page 1. This is
almost easier to do than to read about and the reader is urged to try it. A plot of POD(a)
vs. a is easily created with the spreadsheet plotting tool, highlighting the a
column for the horizontal axis and the Link column for the vertical axis, as in Figures
1-a and 1-b, which also illustrate plotting with logarithmic or Cartesian axes by simply
selecting the desired plotting option. (Although each plot can be printed as a full page,
it is convenient to include the smaller plots as part of the spreadsheet itself.)
[First page] [back] [next] [Spreadsheets]
[References] |
