Thanks:  0
Likes:  0

# Thread: Obtaining an equation from a trend line

1. 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. You mean like...

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

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

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

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

## User Tag List

#### Posting Permissions

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