Extracting trendline equation information to be used in Excel calculat

[peglor]

I'm wondering whether it is possible to take values from the equation Excel calculates for trendlines in a graph and use them in other calculations. I can copy and paste the values as text, but if the graph changes, my copied values do not update. Even getting the contents of the equation text box into a worksheet as a string and parsing that could be of use (Though I've no idea whether Excel has the string parsing ability to do this).

I know it's possible to use the trend function to calculate the trendline slope (For linear interpolation only) without creating a graph, but it would be more convenient to be able to access the trendline equation directly.

 

[IRstuff]

Why not use the results from the TREND worksheet function?

TTFN

 

[pmover]

peglor,

investigate the technique described in the following thread:

thread770-51323

use the linest function.

advise of any questions.

good luck![thumbsup
-pmover

 

[peglor]

Thanks very much for that!

 

[NOEd]

In fact, the equations shown are often rounded off and you must use the linest function.

 

[peglor]

You can get more accurate equations by selecting the text box on the graph with the equation in it and pressing the 'increase decimal places' button. I've never noticed any obvious error in trendlines as added by Excel though.

I've had a quick look at using the Linest function to replace a linear trendline and I'm getting a totally different equation from the linest function than the trendline function gives (For a linear best fit line the slope is off by a factor of about 3).

As far as I can see (Consulting the help file) I'm filling in all the values correctly to the function, so I'm wondering what Linest is doing. Have people had problems?

 

[chrisbullough]

LINEST should be fairly robust, perhaps it is worth asking a few basic questions:

1. are you regressing Y upon X in both cases? (the inverse of the slope of X upon Y is not the same as the slope of Y on X)
2. are there any transformations in your plot? (The exponential function trendline may not give the same answers as taking logarithms and using LINEST - the error model will be different)
3. is your gradient very small or very large?

You can also test the gradient in LINEST by applying the SLOPE function, by applying the Regression Analysis from the Analysis Toolpak add-in, or by setting up the linear regression formulas yourself.