ExcelBanter - Trend line Formulas

"ChrissyBubbles101" wrote:
Subject: Trend line Formulas
Excel has created a formula. And i was wondering
if anyone knew how to access the steps as to how
excel arrived at that formula. I have done it
before, by accident and now I have no idea how.
[....]
The equation/ formula is for a power trend line.

I am quite sure you did not "arrive at the formula ... by accident". Or you
are misusing terminology and asking the wrong question.

First, the power trendline formula has the form y = c*x^b, where c and b are
derived constants. Is that the formula you refer to?

Second, the trendline formula (b and c) is derived from the simple linear
regression model for the x and y data. This is not something you stumble
upon "by accident". See http://en.wikipedia.org/wiki/Simple_regression.

In Excel, b can be derived by the following array-entered formula (press
ctrl+shift+Enter instead of just Enter):

=COVAR(LN(xData),LN(yData))/VARP(LN(xData))

where xData and yData are ranges (or named ranges) that contain the x and y
data.

And c can be derived by the following array-entered formula (press
ctrl+shift+Enter instead of just Enter):

=EXP(AVERAGE(LN(yData))-bFactor*AVERAGE(LN(xData)))

where bFactor is a reference to the cell containing the formula above for b.

More simply, you can derive b and c by selecting two horizontal cells (e.g.
B1 and C1) and array-entering the following formula (press ctrl+shift+Enter
instead of just Enter):

=LINEST(LN(yData),LN(xData))

The result is b in B1 and ln(c) in C1.

Note that C1 is ln(c), not c. So c is EXP(C1).

You can determine points along the trendline (i.e. a best-fit curve, which
might or more likely might not include any of the actual y data) by entering
the following formula:

=EXP($C$1)*X2^$B$1

where X2 is a reference to the cell containing some x value.