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

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

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,

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

Originally Posted by Andy Pope

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

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

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