Excel Trendlines 4

[pmover]

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,LN(x),,),1,2))
b: =INDEX(LINEST(LN,LN(x),,),1)

Exponential Trendline
Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN,x),1,2))
b: =INDEX(LINEST(LN,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

 

[jmw]

Excel-ent, (pardon the pun)!

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

http://www.ViscoAnalyser.com

 

[jmw]

A quick Google finds the source:

http://j-walk.com/ss/excel/tips/tip101.htm

JMW

http://www.ViscoAnalyser.com

 

[jmw]

And what a good site this is.... I am now exploring the "oddities" page and have got to the "What is true and what is False? part..... I'll never trust excel again!
(

http://j-walk.com/ss/excel/odd/odd25.htm)

JMW

http://www.ViscoAnalyser.com

 

[GregLocock]

That just shows that some people don't understand Boolean logic.

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.

 

[HgTX]

Using the "or" operator in Excel gives the correct result of True. The author of the website could have had a few more formulae giving answers such as "TRUE", "1", and "-1".

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

 

[GregLocock]

Exactly

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.

 

[HgTX]

Only if you interpret "+" to mean "or". Me, if I were doing boolean operations, I wouldn't have a "+" anywhere in there. That really invites a reinterpretation of "true" as "1". I could see using a sum to find out how many "true" are in the column in question, in which case addition is the way to go and "2" would be the correct answer in the case given.

Hg

Eng-Tips policies: faq731-376

 

[johnwm]

The examples on the website show a common misapprehension about arithmetic values and boolean values. Using an arithmetic operator on a logic value is unlikely to produce the right answer reliably. It's similar to the common error of using the arithmetic plus operator as a string concatenator. In general MS products interpret ANY non-zero value as TRUE, and zero as FALSE. The only reliable operators for use on Booleans will be the Boolean operators AND, OR, XOR and NOT

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[Wicsteve]

IF YOU DECIDE TO USE THE TRENDLINE EQUATIONS THAT EXCELS PRINTS OUT, BE CERTAIN TO PLOT THE EQUATION TO DETERMINE IF IT REALLY GIVES YOU WHAT YOU WANTED. VERY OFTEN THE EQUATION THAT EXCEL PRINTS IS NOT SHOWN WITH ENOUGH SIGNIFICANT DIGITS AND MORE PRECICION IS REQUIRED.

 

[joerd]

I think you have your CapsLock key stuck (or lost).
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.

 

[ColourfulFigsnDiags]

If you need more precision in the diplayed formula on the chart, you can simply click on the legend box which displays the formula and use the "increase/decrease precision" button at the top.

Read the Eng-Tips Site Policies at FAQ731-376

 

[status]

Please can someone show me how this formula works.

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

 

[status]

Ah - Solved my own problem.

Thanks for the formulae - top dollar!