Hi group,

I have tried the function using it via the worksheet. It worked up to 6
X-input variables. At the seventh variable the whole thing stopped working
and gave #NUM! error. Is the problem caused by too much X variables? Which
is not mentioned in the help file, or is it something to do with problem
definition. Are there other "LSM" possiblities?

Kind regards, Darius Blaszijk

"Darius Blaszijk" schreef in bericht
. nl...
Hello,

I would like to use the linest function in a macro of mine. I have
actually
got it working (example added below). The only problem is that when I
increase the size of x (input data) I get a 1004 error. Doesn't make sense
to me. Is there a limit on the number of parameters that the function can
handle?? Is there anybody that has done a "least squares" macro themselves
that can handle big amounts of data (more than 100 input columns or so)?

Kind regards, Darius Blaszijk

Option Base 1
Sub Test()

Dim y(11, 1) As Double
Dim x(11, 4) As Double

For i = 1 To 11
y(i, 1) = ActiveSheet.Cells(i + 1, 5)
Next i

For c = 1 To 4
For r = 1 To 11
x(r, c) = ActiveSheet.Cells(r + 1, c)
Next r
Next c

t = Application.WorksheetFunction.LinEst(y, x, True, True)

For i = 4 To 1 Step -1
ActiveSheet.Cells(17, i) = t(1, 5 - i)
Next i
ActiveSheet.Cells(17, 5) = t(1, 5)

r = t(3, 1)
ActiveSheet.Cells(17, 6) = r
End Sub