Obtaining an equation from a trend line

[andrew.ward]

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

[Mark W.]

You mean like...

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

[Juan Pablo González]

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.

[andrew.ward]

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

[Ivan F Moala]

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

[Mark W.]

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".