Locating Trendline Equation Factors in Cells

P

Phil Hageman

When adding a trendline on a chart, one option is to add the trendline
equation to the chart. Is there a way to also populate off-chart cells with
the various factors of the equation?

For example: the equation on a chart is: y = 5.1460E+05Ln(x) - 2.5051E+06.
The 5.1460E+05Ln(x) value would be found in cell A1, and the –2.5051E+06
value in cell A2. As values are added to the data matrix and the equation
changes, the cell values would change accordingly.
 
J

Jerry W. Lewis

Alternately, David Braden has posted VBA code to extract the
coefficients (rounded per display - therefore format the chart equation
as Scientific with 14 decimal places) directly from the chart into cells

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

The choice would depend on what version of Excel you are running. In
versions prior to 2003, LINEST/LOGEST/TREND/GROWTH used numerically
inferior algorithms that could give wrong answers in numerically
challenging situations, while the chart trendline algorithms have always
been excellent.

If you have 2003, download the most recent hotfixes and be suspicious if
any coefficients are estimated to be exactly zero.

Jerry
 
P

Phil Hageman

Andy,
Thanks for your reply. I looked at Bernard's examples and don't exactly
understand the big picture... When Excel calculates a logarithmic trendline
(or any other trendline for that matter), it must hold the equation factors
somewhere. That's what I'm after. In the case below (plot data: x=dates, y
= values), the resulting two trendline equation factors (values) change
slightly each period, and that value is what is needed. Or, is a full dress
approach such as Bernard's the only path?
 
A

Andy Pope

Hi,

You could try David's code as suggested by Jerry to extract the info
from the data label.

activechart.SeriesCollection(1).trendlines(1).datalabel.text

Cheers
Andy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top