Using LINEST with arrays as variable (not ranges)

Question

I tried to google myself out of this problem and it seems I can't find any solution to it.

• Basically I have 2 arrays (XValues and YValues) that contain data I should find a polynomial fit to.
• It is a 3rd degree polynomial
• The XValues() as Integer goes from 0 to 359. Its values can start from 0 up to 359 and will increase by 1 each position.
• The YValues() As Double goes from 0 to 359 too and its values go more or less as a parabola from <-3 to 0 and then to <-3 again. Anyway the maximum possible value is 0 and the minimum possible is -80.

Naturally I thought to use LinEst. The syntax of LinEst lets you give "known ys", "known xs" and a couple other parameter I currently don't need.

1. Now, if I use Application.LinEst it seems that my arrays are processed, but I can't force the procedure to give me the coefficient of a 3rd degree polynom (can I?)
2. while if I try with WorksheetFunction.LinEst I can specify the degree, but I cannot apparently work with my variables.

Of course I COULD write my arrays somewhere and then use the second option, but I'd really like to understand if there is a better way...

Answer 1

(1) Polynomial using LINEST and a Chart

(2) Same result using VBA with variants
(note (i) Option 1 uses 1D variants, Option 2 uses 2D (ii) the key piece using Power I saw a while back on a different forum)

 Sub Test()
 Dim Y
 Dim X
 Dim Arr1
 Dim Arr2
 With Application
    Y = .Transpose([a1:a10])
    X = .Transpose([b1:b10])
    Arr1 = .Power(.Transpose(X), Array(1, 2, 3))
    Arr2 = .LinEst(Y, .Transpose(Arr1))
     End With
 MsgBox "coefficients are " & Chr(10) & Join(Arr2, Chr(10))
 End Sub

2B Same result using VBA with 2D variants

 Sub Test2()
 Dim Y
 Dim X
 Dim Arr1
 Dim Arr2
 With Application
    Y = [a1:a10]
    X = [b1:b10]
    Arr1 = .Power(X, Array(1, 2, 3))
    Arr2 = .LinEst(Y, Arr1)
 End With
 MsgBox "coefficients are " & Chr(10) & Join(Arr2, Chr(10))
 End Sub