Weighted Linear Regression

[Dave_AD]

Excel does not do weighted linear regression within scatterplots. Is anyone
out there aware of any VBA that would allow the user to specify regression an
X range against a Y range using a Z range as the weighting range ? THanks for
any help in advance.

 

[PeterA]

Dave,

You don't really need a code to do that. Simply set up your worksheet range to reflect the weights. For instance, if your data is as follows:

Y-range X-range Z-Range
y1 x1 z1
y2 x2 z2
y3 x3 z3
y4 x4 z4
... ... ...
yn xn zn

Recast as:

Y-range X-range
y1/z1 x1/zn
y2/z2 x2/z2
y3/z3 x3/z3
y4/z4 x4/z4
... ...
yn/zn xn/zn

Run normal regression (using VBA or otherwise) on the recast data.

HTH

 

[PBezucha]

Dave,
The best way in alike cases is to google for the term. You would be able to
find for example:
http://www.adamslim.com/ModellingGuides/ModellingGuidesWLR.htm
Regards,
Petr Bezucha