# Thread: Excel '03-function that makes trendline formula?

1. When I make a scatterplot in Excel 2003, and choose Add Trendline, I have the option to show the formula in the chart. I would like to use this formula. The problem is, the only way I know how to do this is to manually type the components of the formula into some cells and manually create the formula. This is sometimes ok, but when the data underlying the scattreplot change, so does the formula. Is there a way I can make a derived trendline formula which is dynamic?

I'd be grateful for any help.

regards

Ken

2. You didn't say what kind of trendline but all the formulas are shown here.

3. Thank you Rory!

4. In addition to the slope/intercept for the line, you can keep them "generic" and all use the LINEST by having:
m: =INDEX(LINEST(y,x),1)
b: =INDEX(LINEST(y,x),1,2)

For all the equations, the Correlation Coefficient (Rē) listed with trendline can be calculated by Indexing on row 3, Column1:
Linear: =INDEX(LINEST(y,x,TRUE,TRUE),3,1)
Logarithmic: =INDEX(LINEST(y,ln(x),TRUE,TRUE),3,1)
Power: =INDEX(LINEST(ln(y),ln(x),TRUE,TRUE),3,1)
Exponential: =INDEX(LINEST(ln(y),ln(x),TRUE,TRUE),3,1)
2nd Order: =INDEX(LINEST(y,x^{1,2},TRUE,TRUE),3,1)
3rd Order: =INDEX(LINEST(y,x^{1,2,3},TRUE,TRUE),3,1)

LINEST also contains other statistics (errors of the constants, F values, etc) by using the proper row and column of the index. The setup of the LINEST output array is in the help...

Steve

#### Posting Permissions

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