Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #3
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Because you cannot directly use a worksheet formula in VBA.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #6
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    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

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
  •