LinEst function

[Darius Blaszijk]

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

 

[Darius Blaszijk]

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

 

[Tushar Mehta]

It is possible there is some limit, but since I just tested with 7
independent variables, it is not 6. My guess would be your variables
are not independent. One of the Xi vectors is a linear combination of
the others.

Also, a tip about transferring data across the XL-VBA interface. For
my test I had x values in A1:G14 and the Y values were in I1:I14. I
wanted the result of linest in A24:H28. The code below does the job:

Sub testIt()
Dim X As Variant, Y As Variant, Rslt As Variant
X = Range(Cells(1, 1), Cells(14, 7))
Y = Range(Cells(1, 9), Cells(14, 9))
Rslt = Application.WorksheetFunction.LinEst(Y, X, True, True)
Range(Cells(24, 1), Cells(28, 8)) = Rslt
End Sub

Do note that all the unqualified references to Range and Cells means
that it works only with data on the active sheet.

It can be generalized even further. Suppose the X and Y data are
demarcated by empty cells around them. In other words the col. to the
right of the X data is empty, followed by the Y data followed by at
least one empty column. In addition, the row below the data set is
also empty. Then, the code below will work:

Sub testIt2()
Dim X As Variant, Y As Variant, Rslt As Variant
X = Cells(1, 1).CurrentRegion.Value
Y = Cells(1, 1).End(xlToRight).End(xlToRight).CurrentRegion.Value
Rslt = Application.WorksheetFunction.LinEst(Y, X, True, True)
Cells(Rows.Count, 1).End(xlUp).Offset(2, 0) _
.Resize(5, UBound(Rslt, 2) - LBound(Rslt, 2) + 1) = Rslt
End Sub

It handles any number of independent variables and any number of data
points!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jerry W. Lewis]

The limit for LINEST is 17 independent variables. MINVERSE can invert a
52x52 matrix, so you could potentially handle 52 independent variables
by manually forming and solving the Normal Equations. I would not trust
the numerical properties of such a solution, though.

Since Excel 2003 has greatly improved the numerical accuracy of LINEST,
it will be interesting to see if the next version of Excel removes this
apparently arbitrary hardcoded limit in LINEST.

Jerry