# Thread: Trying to calculate polynomial trendline coefficients

1. ## Trying to calculate polynomial trendline coefficients

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)

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

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

4. Because you cannot directly use a worksheet formula in VBA.

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

6. ## The Following User Says Thank You to sdckapr For This Useful Post:

arjay13 (2011-05-24)

7. 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 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)")
linearcoeff = Evaluate("=INDEX(LINEST(yPDCBData,xPDCBData^{1,2,3 },0,1),1,3)")

Range("AA34").Value = "X^3"
Range("AA35").Value = "X^2"
Range("AA36").Value = "X"

Range("AB33").Value = "PDCB"
Range("AB34").Value = cubiccoeff
Range("AB36").Value = linearcoeff

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•