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?
Thanks in advance.
Please Login or Register to view this content.
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
I'm not exactly sure of what you mean by this Jerry. Can you elaborate?You will need to construct the appropriate X matrix
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
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 -
> 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
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.a sixth-order polynomial usually overfits the data
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks