VBA Weighted linear regression?

[OldManExcellor]

I use Application.LinEst to create a linear regression in VBA. Is there any formula for a weighted linear regression?

 

[mirabeau]

I use Application.LinEst to create a linear regression in VBA. Is there any formula for a weighted linear regression?

If you don't mind running VBA code ...
(one can make a UDF from a variation of the below, but I'd rather not in this case)

List your y's from Cell A1 downwards,
your x's from Cell B1 downwards
and your Sales from Cell D1 downwards
and see that Column("C") is blank.

don't use headers in any of the columns.

Then run the following VBA code

Sub weighted_regression()
Dim a As Range, n As Long, k As Long
Dim y, X, M, SeCo() As Double
Dim Coeff As Variant, Rsq As Single
Dim W() As Single

Set a = Range("A1").CurrentRegion.Resize(, 4)
n = a.Rows.Count: k = 2
ReDim W(1 To n, 1 To n)
ReDim SeCo(1 To k, 1 To 1)
a.Columns(k + 1) = 1
For i = 1 To n: W(i, i) = a(i, 4) ^ 0.5: Next i
With Application
    y = .MMult(W, a.Resize(, 1))
    X = .MMult(W, a.Resize(, 2).Offset(, 1))
    M = .MInverse(.MMult(.Transpose(X), X))
    Coeff = .MMult(M, .MMult(.Transpose(X), y))
    RVar = (.SumSq(y) - Evaluate(.MMult(.Transpose(y), .MMult(X, Coeff)))) / (n - k)
    Rsq = Evaluate(.MMult(.Transpose(y), .MMult(X, Coeff))) / (.SumSq(y))
    For j = 1 To k
        SeCo(j, 1) = (RVar * M(j, j)) ^ 0.5
    Next j
End With
a.Columns(k + 1).ClearContents

'RESULTS FOLLOW
Cells(1, k + 5) = "Coeffs"
Cells(2, k + 5).Resize(k) = Coeff
Cells(1, k + 6) = "SECoef"
Cells(2, k + 6).Resize(k) = SeCo
Cells(1, k + 7) = "RSq"
Cells(2, k + 7) = Rsq
Cells(2, k + 4) = "b"
Cells(3, k + 4) = "m"
End Sub

If you want the unweighted regression, just put W(i, i) = 1 for the bit colored red, alternatively change all sales values to 1.