Wanted: Non-linear Reg. w/Excel
Jeff Brewster
jbrewster at arserrc.gov
Thu Sep 19 07:23:07 EST 1996
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
More information about the Bio-soft
mailing list