Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
performing regression | New Users to Excel | |||
Polynomial Regression with Dates | Excel Discussion (Misc queries) | |||
The multiple regression tool - linear or non-linear regression? | Excel Discussion (Misc queries) | |||
TREND function for a polynomial fit results in a linear fit | Excel Worksheet Functions | |||
how to get coefficients for Polynomial regression as for rgp() | Excel Worksheet Functions |