Trendline Equation

[Tarra Williams]

MS Excel allows users to create a trendline on a plot
based on a number of points. One option to display is the
equation for the trendline that was created. How can I
extract this equation?? I know copying and pasting the
equation to a cell works, but I'm assuming there's a
better way to get the equation.

Thanks.

 

[Tushar Mehta]

Use the LINEST function. For more on how to use for functions other
than linear graphs see Bernard Liengme's
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm

If you must get the coefficients from the trendline, search
google.com's archive of the XL newsgroups for a post by David Braden on
the subject. David has shared the necessary code for a workaround.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jerry W. Lewis]

As Tushar noted, you can probably get the coefficients from LINEST() or
LOGEST(). If you do not have Excel 2003, then you might also consider
David Braden's VBA code to extract the coefficients directly from the
chart into cells

http://groups.google.com/[email protected]

since LINEST prior to 2003 is not as good numerically as the chart
trendline equation.

Jerry

 

[Will1]

I am currently trying to use Dave Brendans code as I would like to base
formulas on the trendline of the graph so that it will change when
certain information is filtered. However I am unable to make the code
work, not being particularly adept at vb I was just after a little
clarification if you would be so kind.
I have pasted the code into a worksheet and checked all comments are
on the correct lines etc. When I type in =TLcoef(12!,1,1,1) I get an
error on the graph section of the formula. Is my syntax correct? (I
only have one graph, series and trendline)

 

[Bernard Liengme]

Why not use LINEST?

 

[Tushar Mehta]

Expanding on Bernard's suggestion about LINEST, see
Trendline coefficients
http://www.tushar-mehta.com/excel/tips/trendline_coefficients.htm

As far as Dave's code goes, I posted a revised version that corrected
certain limitations. See
http://groups.google.com/group/microsoft.public.excel.charting/msg/0eda3
0f29434786d


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005