Wanted: Non-linear Reg. w/Excel

Jonathan B. Marder MARDER at agri.huji.ac.il
Wed Sep 25 12:04:35 EST 1996


In article <jbrewster.1193523427B at 165.123.35.31>,
   jbrewster at arserrc.gov (Jeff Brewster) wrote:
>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). 
>

Let me modify and add to Jeff Brewster's instructions to exploit some of 
the inbuilt Excel functions.


>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)}

Col. 4 in uncessary. Instead of this you can use the function 
SUMXMY2(yObs,yCalc) to give total sum of squares of differences.
I call this S.S. residual.

>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
..

If you want to do some statistical tests, you can use the function 
DEVSQ(yObs) to get the total sum of squares of deviations for the 
observed data (S.S. total) which would has (No. observations) - 1 
degrees of freedom (d.f.). 

S.S. residual has (No. observations) - N degrees of freedom where N is 
the number of constants in the formula used for calculating yCalc.

S.S. regression is S.S. total minus S.S residual and has N-1 d.f.

Once these are known you can calculate the "M.S. regression" (S.S. 
residual/d.f) and "M.S. residual" (S.S. residual/d.f.). The ratio of 
M.S. regression to M.S. residual is the statistical F-value, which can 
be converted to an error probability via tables or the excel function 
FDIST.
If you use a linear eqn for yCalc, the result is exactly equivalent to 
doing a linear regression.


Jonathan B. Marder             ,      Department of Agricultural Botany
E-mail: MARDER at agri.huji.ac.il |      The Hebrew University of Jerusalem
Phone: (08 or +9728) 9481918   | /\/  Faculty of Agriculture
Fax:   (08 or +9728) 9467763   |/  \  P.O.Box 12, Rehovot 76100, ISRAEL
     http://www.agri.huji.ac.il/~marder




More information about the Bio-soft mailing list