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.

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?)
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...

"cannot apparently work with my variables" what specifically is the error? Please post your code. — hnk, Jul 15 '14 at 10:08
`Coeff = WorksheetFunction.LinEst(YValues, XValues ^ Array(1, 2, 3))` with `Coeff As Variant` doesn't work for example. The error in this case is that the variable doesn't match the returned type (so says vba). There is also not much to post... I'm having the doubt only on this bit of code. Nonetheless I controlled several times how my arrays are populated, and none of them ever gives an error back. — Noldor130884, Jul 15 '14 at 10:13
See http://stackoverflow.com/a/10168632/641067. This uses `Linest` on arrays — brettdj, Jul 15 '14 at 11:23
@brettdj sorry, still not there yet: I want to force LinEst to give 3 coefficient as it was working with ranges — Noldor130884, Jul 15 '14 at 11:27
What do your arrays look like? Can you post sample code and data? — brettdj, Jul 15 '14 at 11:36
It's not that I don't want to post a sample code, it's that I find it somehow useless... I mean, those are just arrays ^_^ How do I populate them does not add any info to the question. Anyway: XValues is an array of Integer, which increase 1 by 1 starting from a number that can go from 0 to 359. Its size is from 0 to 359. YValues is an array of Double, which values can go from 0 to -80. Its size is from 0 to 359. — Noldor130884, Jul 15 '14 at 11:39
It does matter as to what the variants are else I wouldn't have asked .... and you risk having this question closed without making an attempt. I will post a potential solution shortly — brettdj, Jul 15 '14 at 11:48
Well they had to be numbers :P Let's see if you understood the point :P Beware: I know that `Coeff = WorksheetFunction.LinEst(YValues, XValues)` would work, but its results would be m and b of y = mx+b — Noldor130884, Jul 15 '14 at 11:50
The error could be anywhere from how the variables are defined to the kind of data they contain, without the code it's all guesswork and potentially wasted effort on our part. Even if you find the sample code useless, it's obvious you're missing something in the code, so having a look at it would certainly help. — hnk, Jul 15 '14 at 11:58

brettdj · Accepted Answer · 2014-07-15T12:13:54.987

4

(1) Polynomial using LINEST and a Chart

enter image description here

(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

edited Jul 15 '14 at 12:13

answered Jul 15 '14 at 12:03

brettdj

54,857
16
114
177

That's just what I needed! Now, since you were so helpful, can I also ask you why I need to transpose both my arrays in order to make it work? Does it depend on the `.Power`? – Noldor130884 Jul 15 '14 at 12:09
@Noldor130884 good question, the 2D variant is simpler (if your arrays are in that form) as it removes all the `Transpose`s – brettdj Jul 15 '14 at 12:14
1

@brettdj nice point abt `.Power`. One of those obscure things which can make the experience miserable unless you get it right! – hnk Jul 15 '14 at 12:17

Using LINEST with arrays as variable (not ranges)

1 Answers1