Trendline equation and data do not match up

[ASI Lars]

actu.125..Has anyone come across a time when their trendline and data do not
match up?

I have to use a 6th order poly fit on some data I've got. The curve fits
the actual data fine, but when I plot out points using the trendline equation
my data goes crazy.

I have this 6th order equation and x data ranging from 0 to 180 in
increments of 5. the last term of the equation is the y intercept (if x is 0
all other terms fall out) but the trendline eq and the actual data aren't
even close here. The trendline says my y intercept should be .0031, whereas
the actual data (that I created the trendline from) has the y int at .125.
My graph is somewhat sinusoidal... not really but you get the idea, but when
I enter the incremental x data from 0 to 180 into the equation the graph
blows up. The max actual data is around 3 on the y axis and 90 on the x, the
max trendline data is 60 at x = 45. The min actual data is at the y inercept
and is .125 when x = 0 the trendline eq says the min is 3.69 million at x =
180... what gives??!?

 

[Bernard Liengme]

When you raise a number to the 6th power it becomes very large (if it is
greater an one) or very small (if less than 1). So when you multiply this by
one of the coefficients, you need to have a very accurate value for that
coefficients. Just copying from the equation on the chart is not good
enough.

So the answer is to use LINEST : see
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

best wishes

 

[ASI Lars]

How do I get the actual equation to use in a cell to create the LINEST? All
I get is a text box with the equation on the graph. Is that all I need to
copy to a cell?

Also, this tip is listed on the microsoft page "Tip The trendline equation
is rounded to make it more readable. However, you can change the number of
digits for a selected trendline label in the Decimal places box on the Number
tab of the Format Trendline Label dialog box. (Format tab, Current Selection
group, Format Selection button)." I have no number tab to change the decimal
places... if I did would this solve my issue? And if it will, how do I get
this tab? Thanks!

 

[Bernard Liengme]

For a sixth order LINEST, select 7 cells in a row
Let's assume the x-values are in A1:A10 and the y-values in B1:B10
Type =LINEST(B1:B10,A1:A10^{1,2,3,4,5,6}) and commit it with
CTRL+SHIFT+ENTER ( not just ENTER) as it is an array formula
The first cell holds the value for the y^6 term, the last one hold c (the
intercept)