×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Extracting trendline equation information to be used in Excel calculat

Extracting trendline equation information to be used in Excel calculat

Extracting trendline equation information to be used in Excel calculat

(OP)
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.

RE: Extracting trendline equation information to be used in Excel calculat

Why not use the results from the TREND worksheet function?

TTFN

RE: Extracting trendline equation information to be used in Excel calculat

peglor,

investigate the technique described in the following thread:

Thread770-51323

use the linest function.

advise of any questions.

good luck![thumbsup
-pmover

RE: Extracting trendline equation information to be used in Excel calculat

(OP)
Thanks very much for that!

RE: Extracting trendline equation information to be used in Excel calculat

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

RE: Extracting trendline equation information to be used in Excel calculat

(OP)
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?

RE: Extracting trendline equation information to be used in Excel calculat

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources