Obtaining an equation from a trend line

andrew.ward

New Member
Joined
May 5, 2002
Messages
26
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
You mean like...

="y = "&INDEX(LINEST({3;2;17;5;6}),1)&"x + "&INDEX(LINEST({3;2;17;5;6}),2)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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/viewtopic.php?topic=7871&forum=2&4
 
Upvote 0
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".
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top