Excel Trendlines
Excel Trendlines
3
pmover (Mechanical)
(OP)
I happened to be browsing through some old files and came across this one pertaining to trendlines. I've long since forgotten when/where the information was obtained.
Enjoy and good luck!
Chart Trendline Formulas
When adding a trendline to a chart, Excel provides an option to display the trendline equation in the chart. This tip describes how to create formulas that generate the trendline coefficients. These formulas are then used to calculate predicted y values for give values of x.
These equations assume that your sheet has two named ranges: x and y.
Linear Trendline
Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)
Logarithmic Trendline
Equation: y = (c * LN(x)) - b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)
Power Trendline
Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
b: =INDEX(LINEST(LN(y),LN(x),,),1)
Exponential Trendline
Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN(y),x),1,2))
b: =INDEX(LINEST(LN(y),x),1)
2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)
3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)
Higher Order Polynomial Trendline
Notice the pattern in the two preceding sets of formulas
-pmover
Enjoy and good luck!
Chart Trendline Formulas
When adding a trendline to a chart, Excel provides an option to display the trendline equation in the chart. This tip describes how to create formulas that generate the trendline coefficients. These formulas are then used to calculate predicted y values for give values of x.
These equations assume that your sheet has two named ranges: x and y.
Linear Trendline
Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)
Logarithmic Trendline
Equation: y = (c * LN(x)) - b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)
Power Trendline
Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2))
b: =INDEX(LINEST(LN(y),LN(x),,),1)
Exponential Trendline
Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN(y),x),1,2))
b: =INDEX(LINEST(LN(y),x),1)
2nd Order Polynomial Trendline
Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)
3rd Order Polynomial Trendline
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)
Higher Order Polynomial Trendline
Notice the pattern in the two preceding sets of formulas
-pmover





RE: Excel Trendlines
Excel has always bugged me that having added a trend line you then can't use it without lots of messing around. This appears to be exactly what is needed and i must now copy it and save it somewhere that I can find it when I next need it.
JMW
www.ViscoAnalyser.com
RE: Excel Trendlines
http://j-walk.com/ss/excel/tips/tip101.htm
JMW
www.ViscoAnalyser.com
RE: Excel Trendlines
(http://j-walk.com/ss/excel/odd/odd25.htm)
JMW
www.ViscoAnalyser.com
RE: Excel Trendlines
What is the truth of a statement that is True or True or False?
Is it any surprise that Excel gets confused? As I remember in Star Trek an old variant of that question caused someone's head to explode.
Cheers
Greg Locock
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel Trendlines
The confusion here is over what exactly the relationship is between "sum", "+", and Boolean logic. And over why the author insists that "2" is the correct answer. What's "correct" depends on what one is trying to do by ADDING (rather than ORing or ANDing) Boolean values. Should one feel the need to do so, however, the website is useful for explaining what Excel will make of it.
Hg
Eng-Tips policies: FAQ731-376
RE: Excel Trendlines
In Boolean logic
T+T+F =(T+T)+F=T+F=T
or if you insist that it should be interpreted the other way
T+T+F =T+(T+F)=T+T=T
The correct answer is T
Cheers
Greg Locock
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel Trendlines
Hg
Eng-Tips policies: FAQ731-376
RE: Excel Trendlines
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
Steam Engine enthusiasts: www.essexsteam.co.uk
RE: Excel Trendlines
RE: Excel Trendlines
The precision issue is exactly why pmover's suggestion is so good and worth a couple stars.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Excel Trendlines
Read the Eng-Tips Site Policies at FAQ731-376
RE: Excel Trendlines
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)
I've been using the auto generated formula for a trendline, but as mentioned above, It sometimes isn'y very accurate. I would like to create a trend line on the following information, thereafter retrieving the co ordinates enabling me to enter a given x value and returning a predicted Y
X Y
1 114.58%
2 108.36%
3 115.55%
4 111.65%
5 97.81%
6 100.31%
7 98.35%
8 72.05%
9 79.95%
Many thanks chaps
Dylan
RE: Excel Trendlines
Thanks for the formulae - top dollar!