Performing a second order polynomial linear regression in VBA

  • Thread starter Thread starter RyanVM
  • Start date Start date
R

RyanVM

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
 
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 > said:
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:
Click to expand...
Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Ran
ge("C" & MinRow & ":C" & MaxRow),(Range("B" & MinRow & ":B" &
MaxRow))^{1,2}),1)
 
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
 
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
 
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
 
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.
 
Tom said:
*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.
Click to expand...

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