Trying to calculate polynomial trendline coefficients

Topic

New Reply

Hi
I am trying to use a formula to calculate the coefficients from a third order polynomial regression. I am using a formula published by John Wlkanbach which I have seen several places on the internet. For some reason I am getting a syntax error in my macro a portion of which I list below. Anything jump out at you folks?
Thanks
Arjay

Dim cubiccoeff as single

‘name x and y data ranges

Range(“F5”).Activate
Range(ActiveCell, Selection.End(xlDown)).Select
Selection.Name = “yPDCBData”
Range(“E5”).Activate
Range(ActiveCell, Selection.End(xlDown)).Select
Selection.Name = “xPDCBData”
Range(ActiveCell, ActiveCell.Offset(0, 1)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = “PDCBData”

‘syntaxi error at line below
cubiccoeff:=index(linest(yPDCBdata,xPDCBdata^{1,2,3}),1)

Reply | Quote

Replies

Try this:

Dim CubicCoeff As Double
Range(Range(“F5”), Range(“F5”).End(xlDown)).Name = “yPDCBData”
Range(Range(“E5”), Range(“E5”).End(xlDown)).Name = “xPDCBData”
Range(“xPDCBData”).Resize(, 2).Name = “PDCBData”
CubicCoeff = Evaluate(“=INDEX(LINEST(yPDCBData,xPDCBData^{1,2,3}),1)”)

I eliminated the “selections” and also dimmed your variable…

Steve

Reply | Quote

sdckapr
Interesting approach. As a newbie, I am still somewhat less than fluent in VBA. I understand what you did with the named ranges. It also occured to me as I was driving home that I may have Dim_ed vCubiccoeff incorrectly as well as having a “:” in the linest equation. I’ll give it a shot tomorrow morning when I get to work. But why use “Evaluate” instead of just “=” ?
Arjay13

Reply | Quote

Not DIMming the variable was not an issue (DIMming it incorrectly may have been in the future if not done correctly, but was not your problem, I presume you would have DIMmed it as a number or variant). The colon (:) would have also been a problem, (but later, as it never reached this stage…).

The real problem was as Rory indicates, you can’t always use a worksheet formula in VBA, some will work with slight modifications or using Application.WorksheetFunction, but you have an array formula (due to the {1,2,3}) which VBA just doesn’t understand, which required the EVALUATE…

Steve

Reply | Quote

Because you cannot directly use a worksheet formula in VBA.

Reply | Quote

I ended up having to Dim the variables as variant. i also forced the trendline through the origin. It works quite nicely. Thank you so much!
Sub chart()
‘
Dim cubiccoeff As Variant
Dim quadcoeff As Variant
Dim linearcoeff As Variant

‘name data ranges
Range(“F5”).Activate
Range(ActiveCell, Selection.End(xlDown)).Select
Selection.Name = “yPDCBData”
Range(“E5”).Activate
Range(ActiveCell, Selection.End(xlDown)).Select
Selection.Name = “xPDCBData”
Range(ActiveCell, ActiveCell.Offset(0, 1)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = “PDCBData”

‘calculate and print cubic regression coefficients
cubiccoeff = Evaluate(“=INDEX(LINEST(yPDCBData,xPDCBData^{1,2,3 },0,1),1)”)
quadcoeff = Evaluate(“=INDEX(LINEST(yPDCBData,xPDCBData^{1,2,3 },0,1),1,2)”)
linearcoeff = Evaluate(“=INDEX(LINEST(yPDCBData,xPDCBData^{1,2,3 },0,1),1,3)”)

Range(“AD32”).Value = “Trendline Coefficients”
Range(“AA34”).Value = “X^3”
Range(“AA35”).Value = “X^2”
Range(“AA36”).Value = “X”

Range(“AB33”).Value = “PDCB”
Range(“AB34”).Value = cubiccoeff
Range(“AB35”).Value = quadcoeff
Range(“AB36”).Value = linearcoeff

Reply | Quote

You are very welcome. I am glad I could help
The variables should work as doubles. Also the code without selecting (from my original response) is more efficient for setting the names…

Steve

Reply | Quote