Reference 'Equation of Trend Line' Values

**R_S_6** · 03-09-2010, 06:26 AM

Hi all,

I am trying to find out if there is a way to reference the calculated intercept and gradient of a line from a linear trend line created from my data.

For example my line equation maybe:
y = 169341x+27.34

Thus I would like to be able to reference the gradient (169341) and the intercept (27.34) into cells in my spreadsheet. This will allow the ability to modify the graph data and automatically update any formula that I use referring to the intercept and the gradient.

Any help on the above would be gratefully appreciated as always!

Cheers,
Mark

**Andy Pope** · 03-09-2010, 06:54 AM

You can use the LINEST function to return the components of the equation.
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

**R_S_6** · 03-09-2010, 07:37 AM

Thanks Andy.

After a bit more research I found the SLOPE and INTERCEPT functions which are adequate for my needs at the moment.

I did however have a play with the LINEST function first and I could get it to return me the Gradient, just not the Intercept. I think I must be doing something wrong with regards to the Array...? Could you have a look at my example spreadsheet and see where I am going wrong?

Thanks again for your help!
Cheers,
Mark

**Andy Pope** · 03-09-2010, 07:59 AM

Did you read that webpage?

You need to select a range of cells and enter the array formula using CTRL+SHIFT+ENTER in order to get back the multiple values.