Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
LINEST - "Expression too complex"
I am trying to use the linest function to find a sixth order polynomial for a data set of 10 samples in VBA. When I run the code below, I get an error number 16, "Expression too complex". Any ideas about why the expression is too complex for VBA, but not too complex for a worksheet? Code: -------------------- Private Function DoLinest(Data() As Double, ByRef Result() As Double) As Long On Error GoTo FunctionError Dim wsf As WorksheetFunction Set wsf = Application.WorksheetFunction ' Depending on the number of samples passed to the function in DATA(), ' number the x values from 0 to N. Dim intXterms() As Integer ReDim intXterms(0 To UBound(Data)) As Integer Dim intCounter As Integer For intCounter = 0 To UBound(Data) intXterms(intCounter) = intCounter Next intCounter ' The exponents are determined by the second part of the array, what ' would be columns in the worksheet. I am trying to get a sixth order, ' so this array would be {1,2,3,4,5,6} ' NOTE: For clarity, I should have labeled the intCoeff variable as intExponents Dim intCoeff() As Integer ReDim intCoeff(0 To UBound(Result, 2) - 1) As Integer For intCounter = 1 To UBound(Result, 2) ' To 1 Step -1 intCoeff(intCounter - 1) = intCounter Next intCounter Result = wsf.LinEst(Data, intXterms ^ intCoeff, , True) DoLinest = 0 Exit Function FunctionError: Debug.Print err.number Debug.Print err.Description DoLinest = -1007 End Function -------------------- Thanks in advance. -- jnewby72 ------------------------------------------------------------------------ jnewby72's Profile: http://www.excelforum.com/member.php...o&userid=32750 View this thread: http://www.excelforum.com/showthread...hreadid=525828 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
LINEST - "Expression too complex"
For starters, VBA does not support array arithmatic. You will need to
construct the appropriate X matrix instead of trying to compute it on the fly with intXterms ^ intCoeff Jerry "jnewby72" wrote: I am trying to use the linest function to find a sixth order polynomial for a data set of 10 samples in VBA. When I run the code below, I get an error number 16, "Expression too complex". Any ideas about why the expression is too complex for VBA, but not too complex for a worksheet? Code: -------------------- Private Function DoLinest(Data() As Double, ByRef Result() As Double) As Long On Error GoTo FunctionError Dim wsf As WorksheetFunction Set wsf = Application.WorksheetFunction ' Depending on the number of samples passed to the function in DATA(), ' number the x values from 0 to N. Dim intXterms() As Integer ReDim intXterms(0 To UBound(Data)) As Integer Dim intCounter As Integer For intCounter = 0 To UBound(Data) intXterms(intCounter) = intCounter Next intCounter ' The exponents are determined by the second part of the array, what ' would be columns in the worksheet. I am trying to get a sixth order, ' so this array would be {1,2,3,4,5,6} ' NOTE: For clarity, I should have labeled the intCoeff variable as intExponents Dim intCoeff() As Integer ReDim intCoeff(0 To UBound(Result, 2) - 1) As Integer For intCounter = 1 To UBound(Result, 2) ' To 1 Step -1 intCoeff(intCounter - 1) = intCounter Next intCounter Result = wsf.LinEst(Data, intXterms ^ intCoeff, , True) DoLinest = 0 Exit Function FunctionError: Debug.Print err.number Debug.Print err.Description DoLinest = -1007 End Function -------------------- Thanks in advance. -- jnewby72 ------------------------------------------------------------------------ jnewby72's Profile: http://www.excelforum.com/member.php...o&userid=32750 View this thread: http://www.excelforum.com/showthread...hreadid=525828 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
LINEST - "Expression too complex"
You will need to construct the appropriate X matrix I'm not exactly sure of what you mean by this Jerry. Can you elaborate? -- jnewby72 ------------------------------------------------------------------------ jnewby72's Profile: http://www.excelforum.com/member.php...o&userid=32750 View this thread: http://www.excelforum.com/showthread...hreadid=525828 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
LINEST - "Expression too complex"
In addition to Jerry's adivce, Arrays passed to Linest appear to need to be
1 based, not zero based. Here is a working example that returns the coefficients for a 6th degree polynomial. Sub abc() Dim v2 As Variant Dim v() As Double Dim r() As Double Dim v1 As Variant Dim i As Long, j As Long ReDim r(0 To 1, 0 To 6) v2 = Array(5, 15, 89, 851, 4677, _ 17615, 52025, 129939, 287141, _ 577967, 1080825) ReDim v(1 To UBound(v2) - LBound(v2) + 1, 1 To 1) j = LBound(v2) ' make v a 1-based 2-D array ' it will be passed as Date For i = 1 To UBound(v) v(i, 1) = v2(j) j = j + 1 Next v1 = DoLinest(v, r) Debug.Print v1 For i = LBound(r, 2) To UBound(r, 2) Debug.Print i, Application.Round(r(LBound(r), i), 0) Next End Sub Private Function DoLinest(Data() As Double, ByRef Result() As Double) As Long 'On Error GoTo FunctionError Dim wsf As WorksheetFunction Set wsf = Application.WorksheetFunction ' Depending on the number of samples passed to the function in DATA(), ' number the x values from 0 to N. Dim intXterms() As Long Dim v As Variant, r1 As Long Dim v1 As Variant, c1 As Long Dim i As Long, j As Long, k As Long ReDim intXterms(0 To UBound(Data)) Dim intCounter As Integer For intCounter = 0 To UBound(Data) intXterms(intCounter) = intCounter Next intCounter ' The exponents are determined by the second part of the array, what ' would be columns in the worksheet. I am trying to get a sixth order, ' so this array would be {1,2,3,4,5,6} ' NOTE: For clarity, I should have labeled the intCoeff variable as intExponents intCounter = UBound(Result, 2) - LBound(Result, 2) ReDim v1(LBound(Data) To UBound(Data), _ 1 To intCounter) k = 0 For i = LBound(Data) To UBound(Data) For j = 1 To intCounter v1(i, j) = (i - 1) ^ j ' Debug.Print " V1(" & i & "," & j & ")=" & v1(i, j); Next ' Debug.Print Next ' R1 = UBound(v1, 1) - LBound(v1, 1) + 1 ' c1 = UBound(v1, 2) - LBound(v1, 2) + 1 ' Worksheets("Sheet2").Range("A1").Resize(R1, c1).Value = v1 v = Application.LinEst(Data, v1, , True) If IsError(v) Then MsgBox "Bad data" Exit Function End If j = LBound(v, 2) For i = LBound(Result, 2) To UBound(Result, 2) Result(LBound(Result, 1), i) = v(LBound(v, 1), j) j = j + 1 Next DoLinest = 0 Exit Function FunctionError: Debug.Print Err.Number Debug.Print Err.Description DoLinest = -1007 End Function -- Regards, Tom Ogilvy Here is a working example. "Jerry W. Lewis" wrote: For starters, VBA does not support array arithmatic. You will need to construct the appropriate X matrix instead of trying to compute it on the fly with intXterms ^ intCoeff Jerry "jnewby72" wrote: I am trying to use the linest function to find a sixth order polynomial for a data set of 10 samples in VBA. When I run the code below, I get an error number 16, "Expression too complex". Any ideas about why the expression is too complex for VBA, but not too complex for a worksheet? Code: -------------------- Private Function DoLinest(Data() As Double, ByRef Result() As Double) As Long On Error GoTo FunctionError Dim wsf As WorksheetFunction Set wsf = Application.WorksheetFunction ' Depending on the number of samples passed to the function in DATA(), ' number the x values from 0 to N. Dim intXterms() As Integer ReDim intXterms(0 To UBound(Data)) As Integer Dim intCounter As Integer For intCounter = 0 To UBound(Data) intXterms(intCounter) = intCounter Next intCounter ' The exponents are determined by the second part of the array, what ' would be columns in the worksheet. I am trying to get a sixth order, ' so this array would be {1,2,3,4,5,6} ' NOTE: For clarity, I should have labeled the intCoeff variable as intExponents Dim intCoeff() As Integer ReDim intCoeff(0 To UBound(Result, 2) - 1) As Integer For intCounter = 1 To UBound(Result, 2) ' To 1 Step -1 intCoeff(intCounter - 1) = intCounter Next intCounter Result = wsf.LinEst(Data, intXterms ^ intCoeff, , True) DoLinest = 0 Exit Function FunctionError: Debug.Print err.number Debug.Print err.Description DoLinest = -1007 End Function -------------------- Thanks in advance. -- jnewby72 ------------------------------------------------------------------------ jnewby72's Profile: http://www.excelforum.com/member.php...o&userid=32750 View this thread: http://www.excelforum.com/showthread...hreadid=525828 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
LINEST - "Expression too complex"
Looks like the code for that simple worksheet function just got more complicated. Thanks for the replies and solutions. I'll let you know how I do. -- jnewby72 ------------------------------------------------------------------------ jnewby72's Profile: http://www.excelforum.com/member.php...o&userid=32750 View this thread: http://www.excelforum.com/showthread...hreadid=525828 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
LINEST - "Expression too complex"
jnewby72 -
I am trying to use the linest function to find a sixth order polynomial for a data set of 10 ... < In addition to your VBA problems, a sixth-order polynomial usually overfits the data. For a brief discussion, browse to http://www.tushar-mehta.com/excel/ti...efficients.htm and read the section entitled "Over-specifying a regression." - Mike www.mikemiddleton.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
LINEST - "Expression too complex"
a sixth-order polynomial usually overfits the data I've used the linest function in the worksheets on multiple sets of data. I found that I consistently obtained an R² value that was greater than .99 when I used the sixth order. I understand that the R² value is a "rating" of how well the function fits the actual data and a rating of 1 is best. Some data sets fit fine with a third degree and some required higher order fitting. I agree that it is probably overkill in some of the data sets, but I need the precision at times. Thanks for the advice. -- jnewby72 ------------------------------------------------------------------------ jnewby72's Profile: http://www.excelforum.com/member.php...o&userid=32750 View this thread: http://www.excelforum.com/showthread...hreadid=525828 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
LINEST - "Expression too complex"
You may be abusing R-squared. A polynomial of degree n-1 will perfectly fit
(R^2=1) n data points, but will typically be useless for any descriptive purpose, especially for interpolation and extrapolation. To avoid over-fitting of this type, the quality of the fit is typically measured by adjusted R-squared, when the polynomial order is not known a-priori. For a polynomial of order k, the adjusted R-squared is =1-(1-Rsq)*(1+k/dfe). Assuming that a 6th order polynomial does describe the data in some meaningful way, then there is the question of whether the coefficients can be computed with sufficient accuracy to be meaningful. http://groups.google.com/group/micro...9a2bb33e6cdbb8 gives 6th degree polynomial problem that looks innocuous, yet is so numerically difficult, that LINEST in Excel versions prior to 2003 could not give even single figure accuracy for any coefficient! The higher the order of the polynomial, the more likely it is that you will have this kind of numerical difficulty. Not sure why less than half of the posts in this thread are making it into the MS Discussion Groups portal. Jerry "jnewby72" wrote: a sixth-order polynomial usually overfits the data I've used the linest function in the worksheets on multiple sets of data. I found that I consistently obtained an R² value that was greater than .99 when I used the sixth order. I understand that the R² value is a "rating" of how well the function fits the actual data and a rating of 1 is best. Some data sets fit fine with a third degree and some required higher order fitting. I agree that it is probably overkill in some of the data sets, but I need the precision at times. Thanks for the advice. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
VBA: "Volatile" use of concatenated worksheetfunction expression | Excel Programming | |||
Excel expression "sumproduct" in Access | Excel Programming |