Custom Trendline in Excel?

**Ray057** · 04-26-2013, 09:44 AM

Hi.
For a set of data, I need to fit the best curve with the equation of y=x^b; I know excel has the power trendline of y=a.x^b , but I cannot have the "a" coefficient. In general, is it possible to define our custom trendline in excel, rather than using those six predefined functions?
Thanks.

**ragulduy** · 04-26-2013, 10:03 AM

As far as I'm aware, you can't define a custom trendline.

There are curve fitting software programs available that do that kind of thing. I think most come with a free trial if you just need it for a one off.

The alternative would be to use the solver in excel to do the curve fitting, then plot that on the graph if neccessary, for example:
A B C D E
1 x1 y1 A1^E1 abs(C1-B1) 2
2 x2 y2 A2^E1 abs(C2-B2) sum(D1:D4)
3 x3 y3 A3^E1 abs(C3-B3)
4 x4 y4 A4^E1 abs(C4-B4)

Then set the solver to set E2 to 0 by changing E1, it won't be very good, but then if the excel ax^b doesn't give you a value close to 1 for a anyway I doubt the curve fitting will be very close in general!

**MrShorty** · 04-26-2013, 10:21 AM

Solver is an option for almost any regression.

If your equation is linear or linearizeable (if that's a word), then you can do a least squares regression using LINEST() http://office.microsoft.com/en-us/ma...823.aspx?CTT=1

1) y=x^b Take the log of both sides ->
2) ln(y)=ln(x^b)=b*ln(x) substitute u=ln(y) v=ln(x) ->
3) u=b*v
It should be obvious that u as a function of v is a straight line going through the origin.

In this particular case, you are using a special function and Excel provides a function specific to this function, LOGEST() http://office.microsoft.com/en-us/ma...in=HA102927823 note the section about the third/const argument. The above "linearization" is how Excel performs this regression, it just does the u/v substitution for you.