I would like to be able to copy the equation values I get when I add a trendline to a graph. I tried using a macro, but the values are overwritten by the last value found. I have enclosed a spreedsheet with an example of what I would like to do. Thanks for any help in advance.
Use LINEST function of Excel to get the equation coefficients into cells.
I thought the LINEST function was used in the form of y = mx+ B. I need it for an equation that is not linear, but a power equation. I was not able to use the LINEST function to get my desired results. I could be doing it wrong. I am just not sure. Any ideas?
You're correct, LINEST is of the form y=ax+b.
In order to use it for non-linear data, you need to use logs to linearise the equation.
In the case of a Power (y=ax^b) trendline, do this:
=LINEST(LN(y-values),LN(x-values),,TRUE) entered as a 2-cell array formula.
The first cell returned is the slope (b) and can be used directly. The second value is actually LN(a), and to use it you need to transform it back by doinig EXP(a).
I've just read that back, and it's a bit wooly! See the attached, which I hope will show it better! :tumble:
Hope it helps!