how to copy a trendline formula into an active cell ?

bushra · 07-07-2006, 12:00 PM

Is there a way to copy the trendline equation to an active cell outsite the
chart ?

**barrfly** · 11-17-2006, 05:56 PM

assuming it is a straighht line you can use the SLOPE and INTERCEPT function with the data taht was used to create the chart.

the eqation would look like this

=slope(B1:B20,A1:A20) * x + intercept(B1:B20,A1:A20)

where x is either a number or a cell reference to a number

**Bryan Hessey** · 11-18-2006, 05:33 AM

Originally Posted by bushra

Is there a way to copy the trendline equation to an active cell outsite the
chart ?

Hi,

To view the specific formula for a trendline, on the chart, rightmouse the Data Series (to the window that lets you 'Add Trendline'), and one of the 'Trendline' options is 'View Formula on Chart', otherwise you can follow barrfly's post.

hth
---

**juanqui** · 11-25-2006, 01:40 PM

You can use the LINEST function to generate the same formula which is used to plot the trend line in a graph. LINEST an array formula, but I prefer to use the INDEX function to get data from it.

INDEX(LINEST (known Y's;known X's;;TRUE)1,2) returns the intercept value,
INDEX(LINEST (known Y's;known X's;;TRUE)1,1) retuns the multiplication factor
INDEX(LINEST (known Y's;known X's;;TRUE)3,1) retuns the R2 for the regression

If your function is non-linear you'll have to linearise and pass the linearised x and y value to the formula. The inverse linearisation on the result generates the desired values.

for example for a power function:

y = ax^b

ln(y) = ln(a) + b*ln(x)

passing ln(x) as [known x's] and ln(y) as [known y's]
will give ln(a) as intercept (thus exp(ln(a)) = a)
and b as multiplication factor.