Performing a second order polynomial linear regression in VBA
 

Hi, I'm attempting (as the title states) to perform a second orde
linear regression analysis on a set of data.

Where I'm running into trouble is with the {1,2} part of the formula
The VBA compiler complains about the curly brackets being invali
characters. What should I substitute to make it work
Code
-------------------
CurveFitA = Application.WorksheetFunction.Index(Application.Wo rksheetFunction.LinEst(Range("C" & MinRow & ":C" & MaxRow),(Range("B" & MinRow & ":B" & MaxRow))^{1,2}),1
-------------------


--
Message posted from http://www.ExcelForum.com

Performing a second order polynomial linear regression in VBA
 

Sub GetCoefficients()
Dim varr as Variant
varr = Application.LinEst(Range("C2:C11"), Application.Power( _
Range("B2:B11"), Array(1, 2)), True, 0)
For i = LBound(varr) To UBound(varr)
Debug.Print i, Application.Round(varr(i), 2)
Next
End Sub

--
Regards,
Tom Ogilvy

"RyanVM " wrote in message
...
Hi, I'm attempting (as the title states) to perform a second order
linear regression analysis on a set of data.

Where I'm running into trouble is with the {1,2} part of the formula.
The VBA compiler complains about the curly brackets being invalid
characters. What should I substitute to make it work?
Code:
--------------------
CurveFitA =
Application.WorksheetFunction.Index(Application.Wo rksheetFunction.LinEst(Ran
ge("C" & MinRow & ":C" & MaxRow),(Range("B" & MinRow & ":B" &
MaxRow))^{1,2}),1)
--------------------



---
Message posted from http://www.ExcelForum.com/

Performing a second order polynomial linear regression in VBA
 

I'm getting a type mismatch and I can't figure out for the life of m
why.

Code
-------------------
Dim CurveFitParams As Variant
Dim CurveFitA As Single 'Coefficient A of polynomial curve fit equation (Y = AX² + BX + C)
Dim CurveFitB As Single 'Coefficient A of polynomial curve fit equation (Y = AX² + BX + C)
Dim CurveFitC As Single 'Coefficient A of polynomial curve fit equation (Y = AX² + BX + C)
Dim MinRow As Long
Dim MaxRow As Long

CurveFitParams = Application.LinEst(Range("C" & MinRow & ":C" & MaxRow), Application.Power(Range("B" & MinRow & ":B" & MaxRow), Array(1, 2)), True, 0)
CurveFitA = Application.WorksheetFunction.Index(CurveFitParams , 1)
CurveFitB = Application.WorksheetFunction.Index(CurveFitParams , 2)
CurveFitC = Application.WorksheetFunction.Index(CurveFitParams , 3
-------------------
I'm getting a type mismatch on the LinEst line. I've been comparing i
to the code (and modifying it a bit successfully) you posted and can'
figure out what I'm doing different that's causing the problem. Fo
reference sake, below is your (working) code as I've modified it
Again, this works fine
Code
-------------------
Dim varr As Variant
Dim vara As Single
Dim varb As Single
Dim varc As Single
Dim MinRow As Long
Dim MaxRow As Long

MinRow = 2
MaxRow = 11

varr = Application.LinEst(Range("D" & MinRow & ":D" & MaxRow), Application.Power(Range("A" & MinRow & ":A" & MaxRow), Array(1, 2)), True, 0)
vara = Application.Index(varr, 1)
varb = Application.Index(varr, 2)
varc = Application.Index(varr, 3)
Range("A25").Value = vara
Range("A26").Value = varb
Range("A27").Value = var
-------------------


--
Message posted from http://www.ExcelForum.com

Performing a second order polynomial linear regression in VBA
 

Argh, I don't know what I did, but the problem appears to have fixe
itself. I hate when that happens.

Out of curiosity, what type can I define CurveFitParams as so it's no
a variant

--
Message posted from http://www.ExcelForum.com

Performing a second order polynomial linear regression in VBA
 

OK, I figured out why it works sometimes and not others. Now if I coul
figure out how overcome the limitation, things would be even better ;).

The issue arises when the range is more than 2730 data points. I foun
this out by manually playing with MinRow and MaxRow so I'm controllin
the range.

Why would Excel compute the LinEst function just fine if the range i
2730 points or less but with even one point more in the range tell m
that there's a type mismatch

--
Message posted from http://www.ExcelForum.com

Performing a second order polynomial linear regression in VBA
 

See answer to you later posting of this question. When you apply the power
function to the B column, you get a 2D array and 2 x2730 is greater then
5461 which is the limitation in xl97 and xl2000. See second article cited
in the response.

--
Regards,
Tom Ogilvy

"RyanVM " wrote in message
...
OK, I figured out why it works sometimes and not others. Now if I could
figure out how overcome the limitation, things would be even better ;).

The issue arises when the range is more than 2730 data points. I found
this out by manually playing with MinRow and MaxRow so I'm controlling
the range.

Why would Excel compute the LinEst function just fine if the range is
2730 points or less but with even one point more in the range tell me
that there's a type mismatch?


---
Message posted from http://www.ExcelForum.com/

Performing a second order polynomial linear regression in VBA
 

Tom Ogilvy wrote:
*See answer to you later posting of this question. When you appl
the power
function to the B column, you get a 2D array and 2 x2730 is greate
then
5461 which is the limitation in xl97 and xl2000. See second articl
cited
in the response.

--
Regards,
Tom Ogilvy* Thanks for the help once again.

How would I go about defining CurveFitParams so it's not of the Varian
data type. I'm assuming as an array

--
Message posted from http://www.ExcelForum.com

Performing a second order polynomial linear regression in VBA
 

you can't. Only a variant can be assigned an array and Linest is returning
an array.

--
Regards,
Tom Ogilvy

"RyanVM " wrote in message
...
Tom Ogilvy wrote:
*See answer to you later posting of this question. When you apply
the power
function to the B column, you get a 2D array and 2 x2730 is greater
then
5461 which is the limitation in xl97 and xl2000. See second article
cited
in the response.

--
Regards,
Tom Ogilvy* Thanks for the help once again.

How would I go about defining CurveFitParams so it's not of the Variant
data type. I'm assuming as an array?


---
Message posted from http://www.ExcelForum.com/