Logit Spreadsheet

 

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]

 
SElogo.gif (5955 bytes)

[ HOME ] [ Feedback ]

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