Power Trendline Equation used in a cell

**Ginge1267** · 02-07-2014, 09:11 PM

Hi Chaps,

I have some graphs which use power trendlines, these change as I add more and more test data. I've looked just about everywhere and nothing seems to come close. LINEST is for straight line graphs (or I'm doing it wrong) and doesn't seem to give me what I need. I've attached the sheet I'm working on and hopefully it'll open on the page and you'll see where I'm trying to use it. On tab "Tare Full Service Plot" if I can link cells U3 and U4 to the graph equation it will solve all my problems.

Any Ideas?

Thanks in advance

Ginge

**tngengineer** · 02-08-2014, 12:12 AM

Great question. I am interested in seeing this solved.
I have worked around this by using helper columns and using Solver to minimize error squared. Solver calculates the coefficients for whichever equation you specify. Unfortunately, the Solver solution does not exactly match the routine Charts uses to calculate trendlines. Using this workaround, I have to re-run Solver when the data changes.
Hopefully, someone here has a more elegant solution.

**MrShorty** · 02-08-2014, 12:36 AM

Every one of the built in chart trendline options is "linear" or can be made linear. In these cases, linear refers not just to straight lines, but to linear in a "linear algebra" sense.

In the case of a power trendline -- according to Excel help, this is the equation:
y=cx^b
take the log of both sides
ln(y)=ln(cx^b)=ln(c)+ln(x^b)=ln(c)+b*ln(x)
If you don't follow the logic there, I would suggest a review of the properties of logarithms and exponents: http://www.purplemath.com/modules/logs.htm
to help see the transformation, subsitiute v=ln(y) and u=ln(x) and k=ln(c). The equation now becomes:
v=k+b*u
which should obviously be a straight line equation. We can now use the linear regression algorithms in LINEST() to get k and b. Then a little algebra to get back to the original y=c*b^x equation.

**Ginge1267** · 02-08-2014, 07:35 AM

Thanks MrShorty,

I will go and have a read, I've never worked logarithms so I'll see if I can work it out. In the mean time, if you could let me have some steps towards working it out I really would appreciate it. I need to get it sorted by Monday if at all possible.

Thanks for the suggestion, its good to know there may be a way forward.

Ginge

**MrShorty** · 02-08-2014, 11:01 AM

I've never worked logarithms so I'll see if I can work it out.

I find this hard to believe. I mean, on this side of the pond, they talk all the time about how woefully inadequate our math education is compared to other industrialized nations. Our kids learn logs in high school algebra/pre-algebra classes. Unless you are that young still, I expect it is more likely that you have just forgotten having done logs in school and need a little refresher course, which something like the purplemath page should be able to give you.

As for steps towards working it out, there really isn't much to tell. Generic skeleton approach:

1) Take the log of the y's and store the values somewhere
2) Take the log of the x's and store the values somewhere
3) Invoke the regression algorithm (in Excel, this will be the LINEST() function) using these helper arrays/columns/rows
4) Adjust the values output by the regression algorithm into the desired format.

**shg** · 02-08-2014, 11:38 AM

Fine exposition by Mr S. I would only add that you don't need to tabulate the logs separately:

	A	B	C	D	E	F
1	x	y		m	b
2	1	5.00	B2: =5*A2^1.07	1.070	1.609	D2:E2: {=LINEST(LN($B$2:$B$11), LN($A$2:$A$11))}
3	2	10.50			5	E3: =EXP(E2)
4	3	16.20
5	4	22.04
6	5	27.98
7	6	34.01
8	7	40.11
9	8	46.27
10	9	52.48
11	10	58.74

Col B contains a power series. LINEST returns the regression ln(y) = m ln(x) + b,

so y = exp(m ln(x) + b) = exp(b) x^m (after you brush up on your logs)

**Ginge1267** · 02-08-2014, 12:04 PM

Hi MrShorty,

I'm 46, its been a while true, but I don't remember doing logarithms. You might be right and I've just forgotten them. Interesting what you say about inadequate your math education is compared to other industrialized nations, they say exactly the same here. The purplemath pages are good I'm working through them, but also trying to recover from coming off a night shift and getting very little sleep is exacerbating the problem, I may start afresh tomorrow morning.

Thanks to both you and shg for your help, it really is very much appreciated.

Ginge

**Ginge1267** · 02-10-2014, 05:26 AM

Damn, why is this so difficult, if only microsoft could give it a reference!!!

**Ginge1267** · 02-10-2014, 07:01 AM

Dear shg,

In your table above, cell C2 "B2: =5*A2^1.07" what does the symbol ^ mean, does it make 1.07 a power? e.g. =5*(POWER(A2,1.07)) when written in excel?

P.s. I'm trying to teach myself excel at the same time here.

Ginge

**shg** · 02-10-2014, 11:36 AM

Yes, same.

**Ginge1267** · 02-10-2014, 01:53 PM

Dear shg,

Please see the attached learning.xlsx I've been trying to work through your example above on sheet1 and apply it to my own data on sheet2. Please have a look and let me know where I'm going wrong. Cells D2 E2 and E3

P.s. logarithms are just not going in, I seem to be better with a worked through example.

Ginge

**shg** · 02-10-2014, 02:35 PM

Select D2:E2, put the cursor in the formula bar, press and hold Ctrl and Shift, then press Enter.

**Ginge1267** · 02-10-2014, 02:38 PM

Hi shg,

Ok it worked, what did "press and hold Ctrl and Shift, then press Enter" actually do?

Ginge

**MrShorty** · 02-10-2014, 02:44 PM

deleted -- late to the game

**Ginge1267** · 02-10-2014, 02:48 PM

Hi MrShorty,

Actually that was starting to make sense, I don't know what an array is, I've never used them before.

Ginge

**Ginge1267** · 02-10-2014, 03:16 PM

Dear MrShorty and shg,

I have come to the end and conclude this is above me. I can't get my head around anything other than the most basic logarithms, I just don't have the time to study them now. I obviously don't know enough about excel and its many functions. The worked examples I start to get then I don't understand why they don't work. Should I have known it was an array? I'm just going to have to do it manually until I have the time to try again, I should be resting as I'm out testing all tonight.

Thank you very much for your help, I will continue to follow in case others ask the same question.

**shg** · 02-10-2014, 03:48 PM

There are a ton of resources if you Google excel array formulas. You might start with http://www.cpearson.com/excel/ArrayFormulas.aspx

Power Trendline Equation used in a cell

LinkBack

Thread Tools

Rate This Thread

Display

Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Re: Power Trendline Equation used in a cell

Thread Information

Users Browsing this Thread

Similar Threads

Accessing chart trendline equation with VBA or cell formula

Power trendline

Discrepancy between data analysis regression equation and linear trendline equation

Display Trendline Equation in Other Cell

How to link trendline equation in graph to cell?

Bookmarks

Bookmarks

Posting Permissions