Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
LinEst function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
LinEst function
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
LinEst function
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.Val ue 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 In article , says... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
LinEst function
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 Tushar Mehta wrote: 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.Val ue 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deriving P-Value through LINEST Function | Excel Worksheet Functions | |||
linest function for curve | Excel Discussion (Misc queries) | |||
LINEST Function | Excel Discussion (Misc queries) | |||
Linest function | Excel Worksheet Functions | |||
LinEst embedded in VBA Function | Excel Programming |