Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Obtaining an equation from a trend line

  1. #1
    New Member
    Join Date
    May 2002
    Location
    New Zealand
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all,

    I have plotting a trend line through some raw data. There is an option that allows me to display the equation for this trend line.

    I need to use this trend line to create some interpolated values.

    I am able to manually copy the equation from the chart into a cell. However, is there a way of doing this automatically (via VBA, excel functions or some other means) so that the equation changes when the raw data and consequential trend line change?

    Thanks heaps,
    Andrew

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You mean like...

    ="y = "&INDEX(LINEST({3;2;17;5;6}),1)&"x + "&INDEX(LINEST({3;2;17;5;6}),2)

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think you can get this equation with some math (Altough, I know that you can "get" it using VBA)...

    For example, doing a simple chart {1,2,3}, I added a linear trendline, displayed the equation, and in VBA, in the inmediate pane, i used:

    ?ActiveChart.SeriesCollection(1).TrendLines(1).DataLabel.Text
    y = x

    That way, you can adapt it to your needs.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    New Member
    Join Date
    May 2002
    Location
    New Zealand
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Great. The text method works really well. Thank you.

    However, can I evaluate this formula directly using some cool VBA function (see text from another posting)? Or do I need to use the laborious string functions?

    Other Posting:

    Hi all,

    I have formula as a text string. Is there a method of evaluating this equation directly in VBA rather than using string manipulation functions to extract the numerical values in order to create a new function?

    That is, I have can obtain the equation of a trendline as a string. Can I evaluate this directly using some type of string evaluation function, and if so how? Or do I have to use the laborious string functions to extract the coefficients and recreate the function using variables?

    E.G.

    Dim x as double
    Dim y as double
    Dim TrendlineEquation as string

    TrendlineEquation = “2.6667*x + 11.44”

    x=2

    y= SomeTypeOfEvaluationFunction (TrendlineEquation)

    ‘Or to put it another way:

    y= SomeTypeOfEvaluationFunction (“2.6667*x + 11.44”)

    ‘Nb the value of ‘y’ should be 16.77

    Thanks so much,
    Andrew

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-09 18:08, andrew.ward wrote:
    Great. The text method works really well. Thank you.

    However, can I evaluate this formula directly using some cool VBA function (see text from another posting)? Or do I need to use the laborious string functions?

    Other Posting:

    Hi all,

    I have formula as a text string. Is there a method of evaluating this equation directly in VBA rather than using string manipulation functions to extract the numerical values in order to create a new function?

    That is, I have can obtain the equation of a trendline as a string. Can I evaluate this directly using some type of string evaluation function, and if so how? Or do I have to use the laborious string functions to extract the coefficients and recreate the function using variables?

    E.G.

    Dim x as double
    Dim y as double
    Dim TrendlineEquation as string

    TrendlineEquation = “2.6667*x + 11.44”

    x=2

    y= SomeTypeOfEvaluationFunction (TrendlineEquation)

    ‘Or to put it another way:

    y= SomeTypeOfEvaluationFunction (“2.6667*x + 11.44”)

    ‘Nb the value of ‘y’ should be 16.77

    Thanks so much,
    Andrew
    Hi Andrew I posted a soluton to this in a latter post...but you had alreay solved it.

    Solved here;
    http://www.mrexcel.com/board/viewtop...7871&forum=2&4

    Kind Regards,
    Ivan F Moala From the City of Sails

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you want to solve for "y"...

    =INDEX(LINEST({3;2;17;5;6}),1)*A1+INDEX(LINEST({3;2;17;5;6}),2)

    ...where A1 contains your value for "x".

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •