Add a Trendline

[sdruley]

Let's say that I add a trendline to points already on my graph. How can I get the coefficients and shaping parameters of that equation onto my worksheet? For example, (Y = 2.3x^1.6 + 1.1). I need the 2.3, 1.1 and the 1.6 on the worksheet since I already know the equation type or format?

 

[joeu2004]

@sdruley.... Usually, I would suggest that you use LINEST or similar function to calculate the trendline coefficients in Excel directly.

But I'm not familiar how to do that for a trendline of the form y = b*x^m + c. (In Excel 2010, the power trendline does not have the "+ c" component.)

If you actually have a trendline of the form y = b*x^m, you can array-enter =LINEST(ln(Y),ln(X)) into 2 horizontal cells to calculate the coefficients m and b, in that order.

Alternatively, format the trendline label as Number with 14 decimal places, in your example, and copy each coefficient into cells in Excel from the trendline label.

I recommend copying the coefficients with the highest precision that Excel will format, namely 15 significant digits.

To that end, usually I format the trendline label as Scientific with 14 decimal places. That displays 15 significant digits, regardless of the magnitude of the coefficient.

 

[montecarlo2012]

Hello, your equation is y = m * x + b and what you write in your question is the display on your spreadsheet. I would line to know which line you want to use and upload some data from your spreadsheet.
As example for example to use

range("L10").formula="=index(linest(B3:B37,LN($A:$3:$A$37)),1,2)"

like I said, upload a little info on your sheet, remember, location is everything, so I can see where is your Y's and X's
and which one you want to use
Logarithmic, Power, 2nd Order Polynomial etc.

 

[sdruley]

Thank you Joeu for you excellent help

 

[sdruley]

montecarlo,
Thanks for your input but problem has been resolved

 

[montecarlo2012]

Thanks for the feedback. welcome