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 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

 

[Jerry W. Lewis]

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]

You will need to construct the appropriate X matrix

I'm not exactly sure of what you mean by this Jerry. Can you elaborate?

 

[Tom Ogilvy]

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.

 

[jnewby72]

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.

 

[Mike Middleton]

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/tips/trendline_coefficients.htm

and read the section entitled "Over-specifying a regression."

- Mike
www.mikemiddleton.com

 

[jnewby72]

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.

 

[Jerry W. Lewis]

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/microsoft.public.excel/msg/969a2bb33e6cdbb8
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