BGHoneywell -
I am looking for help on how to automatically extract the constants
generated by doing a power curve fit to data. I know how to make the
equation of a power curve be displayed, but I would like to extract the
values directly. All help appreciated. <
If you want VBA code to extract the trendline coefficients, Dave Braden
provided it in ancient messages. To find his solution, go to Google Groups
and search for "excel trendline coefficients braden" (without the quotes).
For a solution using worksheet functions, to get the coefficients for the
power function with form y = c*x^b,
c = EXP(INTERCEPT(LN(yrange),LN(xrange)))
b = SLOPE(LN(yrange),LN(xrange))
- Mike
www.mikemiddleton.com
P.S. To help you understand the solution, here's an edited excerpt from my
book, Data Analysis Using Microsoft Excel: Updated for Office XP.
The power model creates a trendline using the equation
y = c*x^b.
Excel uses a log transformation of the original x and y data to determine
fitted values, so the values of both the dependent and explanatory variables
in your data set must be positive. If any y or x values are zero or
negative, the Power icon on the Add Trendline Type tab will be grayed out.
(As a workaround, you can add a constant to each y and x value.)
The power trendline feature does not find values of b and c that minimize
the sum of squared deviations between actual y and predicted y (=c*x^b).
Instead, Excel's method takes the logarithm of both sides of the power
formula, which then can be written as
Ln(y) = Ln(c) + b * Ln(x),
and uses standard linear regression with Ln(y) as the dependent variable and
Ln(x) as the explanatory variable. That is, Excel finds the intercept and
slope that minimize the sum of squared deviations between actual Ln(y) and
predicted Ln(y), using the formula
Ln(y) = Intercept + Slope * Ln(x).
Therefore, the Intercept value corresponds to Ln(c), and c in the power
formula is equal to Exp(Intercept). The Slope value corresponds to b in the
power formula.
