Power Trendline Equation

[chastst]

I have a chart which I used a power trendline. While I can look at the equation and type the values into a worksheet for other use, is there a way to retrieve the constants through VBA or other means?

Thanks

 

[tusharm]

The power trendline is given by y=c*x^b. Believe it or not, you can use LINEST to calculate the parameters. Here's how.

Take the log of the above equation to get ln=ln(c)+b*ln(x). This is a linear equation! So, LINEST will give you ln(c) as the intercept and b as the slope.

Suppose your x and y values are in columns A and B. Then, in C and D, calculate LN() of the cells in columns A and B respectively. Now, use LINEST with the cells in C and D to get the slope and the intercept. The slope is b in the power trendline and Exp(intercept) is c.

 

[chastst]

Thanks for the response tusharm. The answer you gave works in general when plotting data. However, I am using the power trendline to approximate a straight line on a log-log plot. I cannot come up with the same constants as the equation no matter how many combinations of ln and log I try. Any thoughts on how I can get those constants using the linest function?

Thanks

 

[tusharm]

I'm sorry but I don't understand the problem you are having. A log-log *display* of the data doesn'tchange the underlying analysis. If you plot a bunch of X,Y pairs and ask for a power trendline, you will get the same result irrespective of whether the plot shows either or both of the axis on a log scale. In addition, if you use array enter =LINEST(LN(Y-range),LN(X-range),TRUE,TRUE), in a 5x2 range, the first first cell in the first row will be b, and EXP(1st row 2nd cell) will be c -- as in y=c*x^b.

If it still doesn't work for you, why don't you share how you have the data organized in the spreadsheet, what the power trendline results show, how you entered the LINEST function, and what result it gives.

 

[chastst]

Thanks tusharm - I got it! I won't tell you what dumb mistake I was making, but it does work.

chastst

 

[sweetmetrics]

OK, I am trying to solve the exact same problem that chastst is, except I am dumber, well, a lot less smart. I am cursed with desperately trying to catch up from not taking math or statistics in college. Anyway, I am doing a lifetime value analysis and my data set shows a series of transactions starting off strong and steadily declining over time. The power curve trendline seems to have the closest R-square value (again still trying to learn what that is) to 1 and well, it just looks closer than any of the other trendlines.

I have the one column representing decreasing transactions each month that look like this:

62
8
3
2
2
2
1
2
2
2
2
1
2
2

I need to forecast the rest of the values through 48 months. I've tried exponential smoothing, moving average, and even the forecast function, but all of them don't seem to produce the result that seems to make as much sense as the power curve.

I'd love to be able to decipher what tusharm is describing in this post to the point where I could replicate this myself.

Would love any direction on how to get to the finish line.

Thank you in advance.

 

[sweetmetrics]

Ah hah, I found a great case study online that helped me understand this. Assuming it was published by you tusharm or just a really interesting coincidence.

Thank you.

Shilo