Wanted: Non-linear Reg. w/Excel

[Jeff Brewster]

In Article <bornemd-1809961553560001 at dom015.dom.aa.wl.com>,
bornemd at aa.wl.com (Dirk Bornemeier) wrote:
>In the distant past (when I used to read this group fairly regularly) I
>remember some postings describing ways that Excel could be used to do
>non-linear regression (using Solver).  I didn't need that then, but now
>that I do I can't find anything about it from web searches, Microsoft,
>Baarns, etc..  Help ... 
>
>I don't need someone to tell me this _can_ be done in theory - I know it
>can - I'm hoping to find someone who has alerady done this and is willing
>to 'give it away'.
>
>
>Thanks for reading this far :-).
>
>
>Replies by e-mail requested.
>
>
>Dirk Bornemeier
>bornemd at aa.wl.com

Let yCalc = f(x,p1,p2,p3....)
where x is the independent variable, p1, p2, etc. are parameters, and f() is
a user-supplied function which calculates the value of the dependendent
variable (yCalc) given x, p1, p2, ...

Given initial guesses for the parameters and a table of values for x and
observed y values yObs, find new values for the parameters which minimize
the sum of the squared differences, i.e. minimize e where
e = Sum((yObs - yCalc)^2)

A.  Put the initial guesses of each of the parameters in separate cells
    of the spreadsheet.
    
B.  Make a table with the following columns:
    Col 1: x values (independent variable)
    Col 2: yObs values (observed dependent variable)
    Col 3: yCalc values (dependent variable calculated using a formula
           supplied by you)
    Col 4: squared difference values {enter the formula =(C3-C2)^2}
C.  Create a cell that contains e, the sum of the cells in Col 4 {=SUM(A4:A12)}
D.  Open Solver from the menu.
E.  In response to the prompt "Set target cell:" enter the cell reference
    for e (the cell created in step C)
F.  In response to the prompt "By changing the cells:" enter the cell reference
    for the parameter cells (the cells created in step A)
G.  Press "Solve"
Jeff Brewster
Philadelphia USA
jbrewster at arserrc.gov