weighted trendline

[Always need excel help]

Can anyone tell me if it possible to weight a linear trendline in excel?
Like a calibration line where you can weight by 1/x where x is the variance
at each point on the line.

 

[Shane Devenshire]

Hi,

I don't believe that can be done with anything built into the trendline
chart feature, but you can use the trendline formula in the spreadsheet and
then apply your weighting to it. Then plot that rather than the built-in
trendline. You can get the formula of the trendline from the chart by
choosing to display it or in the spreadsheet by using the LINEST function or
the related SLOPE and INTERCEPT functions.

 

[Always need excel help]

Hi,

Many thanks for your quick reply but want to make sure I am going to do this
correcly.
I know how to get the trendline formula. ok there.
The weighting factor will be different for each point on the line and I
guess I can calculate them x(trendline)=y-c/m from the trendline and my
weight for each point will be w = 1/ (x(trendline) - x(measured)) ie. inverse
of the variance at each point. But how do I apply it back to the trendline
to plot this new weighted line? Just multiply my measured x values by w in a
new column and plot these?

I am basically trying to remove heteroscedasticity (increasing variance)
across my regression line.
Thanks for any further help you can give.

 

[Bernard Liengme]

Can you scan a page or two of a textbook that explains this weighting
(hopefully with example) and send files to my private email (remove
TRUENORTH. or visit website to get my real email address)
best wishes

 

[PBezucha]

Somebody else's plume from my notepad that follows Bernard's advice, still it
should serve:

Function WLR(YRange As Range, XRange As Range, WeightRange As Range)
'calculates the weighted linear regression - returns an array {a,b}
{slope,intercept}
'by Adam Slim

Dim SigmaW As Double, SigmaWX As Double, SigmaWX2 As Double
Dim SigmaWY As Double, SigmaWXY As Double
Dim i As Long, outWLR(1 To 2) As Double

'validate ranges
If XRange.Count <> YRange.Count Or XRange.Count <> WeightRange.Count Then
'fails - the ranges must be the same size
WLR = CVErr(xlErrRef)
Exit Function
End If

'calculate the sigmas
For i = 1 To XRange.Count
SigmaW = SigmaW + WeightRange.Cells(i).Value
SigmaWX = SigmaWX + WeightRange.Cells(i).Value * XRange.Cells(i).Value
SigmaWX2 = SigmaWX2 + WeightRange.Cells(i).Value * XRange.Cells(i).Value ^ 2
SigmaWY = SigmaWY + WeightRange.Cells(i).Value * YRange.Cells(i).Value
SigmaWXY = SigmaWXY + WeightRange.Cells(i).Value * XRange.Cells(i).Value *
YRange.Cells(i).Value
Next i

'calculate the outputs
outWLR(1) = (SigmaWX2 * SigmaWY - SigmaWX * SigmaWXY) / (SigmaW * SigmaWX2 -
SigmaWX ^ 2)
outWLR(2) = (SigmaW * SigmaWXY - SigmaWX * SigmaWY) / (SigmaW * SigmaWX2 -
SigmaWX ^ 2)
WLR = outWLR
End Function