weighted least square regression

[Guest]

How can weighted least square regression be done in Excel?

 

[Tushar Mehta]

See
http://www.mathworks.com/access/helpdesk/help/toolbox/curvefit/ch_fitt5
..html

Assume that the actual x-values range is named XVals, the X values
range including the column of all 1s is named X, the weights range is W
and the Y values range is Y.

Then select the same number of consecutive cells in a column as the
number of columns in name X, and enter the *array* formula
=MMULT(MINVERSE(MMULT(TRANSPOSE(X),W*X)),MMULT(TRANSPOSE(X),W*Y)).
The above formula assumes that all the necessary computations can be
done safely.

Alternatively, suppose you want the coefficients for a single
independent variable relationship (i.e., y=ax+b) in H2:I2. Then, in
some cell enter =SUMPRODUCT(W*(Y-(H2*XVals+I2))^2). Use Solver to
minimize the value in this cell by changing the values in H2:I2.

Both methods are easily extended to an analysis of multiple variables,
X1, X2, etc.

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jerry W. Lewis]

For weighted linear regression of a single variable,

http://groups.google.com/groups?selm=3FFC11D7.3060403@no_e-mail.com

gives formulas that are more numerically stable than Aitken's extension
of the normal equations.

Jerry