Closed Thread
Results 1 to 10 of 10

How does this code output the coefficients of a trendline?

  1. #1
    Forum Contributor
    Join Date
    03-04-2008
    Location
    Ohio
    MS-Off Ver
    Office 2010
    Posts
    208

    How does this code output the coefficients of a trendline?

    Many thanks to Andy Pope for posting the links to this code. I'm trying to get the coefficients of several trend lines, on multiple sheets. I don't fully understand all of this code, but I get the basics. My first question is, "Where does the code output the coefficients of the trend lines?"

    Please Login or Register  to view this content.
    Any help would be appreciated. This one is WAY over my head.

    Thanks,

    -gshock

  2. #2
    Forum Contributor
    Join Date
    03-04-2008
    Location
    Ohio
    MS-Off Ver
    Office 2010
    Posts
    208

    Additional questions

    Also, from the code above, it seems like it's written to handle one sheet at a time. Does that mean I need to create new modules in VB for each sheet that has a trend line, who's coefficients I want to get?

    I really appreciate anyone's help on this. I don't really understand how this particular code works.

    Thanks in advance,

    -gshock

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,657
    The function takes arguments that specify which sheet, chart, series and trendline to use.

    You may need code to create a loop to process multiple items.

    Why code and not the Linest function?
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Contributor
    Join Date
    03-04-2008
    Location
    Ohio
    MS-Off Ver
    Office 2010
    Posts
    208
    Andy, I understand the arguments the code takes. I just don't understand where to get the data from once the code has taken the coefficients. I want to read be able to populate a single cell with the coefficient for each variable.

    I didn't use LINEST because I thought this might be easier as a "canned" implementation. Appears it's not so simple. Also, the author mentioned that the accuracy is better for Trend lines than for LINEST. Is this not true?

    Looks like I should go back to LINEST instead.


    Thanks,

  5. #5
    Forum Contributor
    Join Date
    03-04-2008
    Location
    Ohio
    MS-Off Ver
    Office 2010
    Posts
    208
    Andy, I understand the arguments the code takes. I just don't understand where to get the data from once the code has taken the coefficients. I want to read be able to populate a single cell with the coefficient for each variable.

    I didn't use LINEST because I thought this might be easier as a "canned" implementation. Appears it's not so simple. Also, the author mentioned that the accuracy is better for Trend lines than for LINEST. Is this not true?

    Looks like I should go back to LINEST instead.


    Thanks,
    -gshock

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,657
    The function, TLcoef, returns an array.
    Each element in the array is a part of the formula.

    I read the author's comment as saying Linest can be more accurate,

    " With XL2003 LINEST may even return more accurate results than the trendline."

  7. #7
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373
    Quote Originally Posted by Andy Pope View Post
    I read the author's comment as saying Linest can be more accurate,

    " With XL2003 LINEST may even return more accurate results than the trendline."
    Hi Andy

    I never found any difference between the trendine's coefficients and the Linest() output, for any type of regression curve chosen.

    Would you have an example where that happens?

    Thanks
    lecxe

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,657
    Sorry, I do not have a data set that illustrates the differences.

  9. #9
    Forum Contributor
    Join Date
    03-04-2008
    Location
    Ohio
    MS-Off Ver
    Office 2010
    Posts
    208
    For my needs, LINEST is working nicely. I'm trying to work on some formatting changes, but the output is nearly identical to what comes from the Trendline.
    I didn't know that LINEST existed until I asked about this code.

    Thanks for the help.

    Regards,

    -gshock

  10. #10
    Registered User
    Join Date
    04-13-2010
    Location
    Lawrence, KS
    MS-Off Ver
    Office Excel 2007
    Posts
    1

    Re: How does this code output the coefficients of a trendline?

    Gshock,

    The code you are referring to must be inputted into a module in VBA. The code defines two new functions (tlcoef, and tleval) which can be used to extract the trendline equation information. Instructions are included in the code which tell you how to use the functions. For coefficients, select your array of cells (this depends on how many coefficents the equation contains) and type in "=tlcoef("Sheet Name In Quotes","Chart Name in Quotes" or chart number,"Series Name in Quotes" or series number,Trendline Number) and hit control+shift+enter and the coefficient data will appear. It will also automatically update when the data is changed.

    Thanks,

    aaronmic

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Code Execution Degrades Over Time
    By KDT in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-25-2008, 04:28 PM
  2. Placement of a particular line of code
    By smurray444 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-19-2007, 11:15 AM
  3. Running code while displaying a UserForm with vbModeless
    By PilgrimTim in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-06-2007, 08:08 AM
  4. Problems understanding automated emailing code.
    By DDONNI in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-19-2007, 12:26 PM
  5. New to Userforms - need help on the output
    By Tibbs in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-17-2006, 09:35 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1