Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trendline equation and data do not match up
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??!? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trendline equation and data do not match up
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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "ASI Lars" <ASI wrote in message ... 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??!? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trendline equation and data do not match up
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) -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "ASI Lars" wrote in message ... 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" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "ASI Lars" <ASI wrote in message ... 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??!? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trendline equation doesn't fit data | Excel Discussion (Misc queries) | |||
Trendline equation calculation | Charts and Charting in Excel | |||
trendline equation | Charts and Charting in Excel | |||
Trendline Equation | Excel Worksheet Functions | |||
Excel graphed trendline does not match derived equation | Charts and Charting in Excel |