trend line equations

[katy09]

I have made a graph of some data in excel (temp vs. year) and added a linear
and quadratic trend line. When I take the equations from the trendlines and
try to project into the future by pluggin in higher x values into the
equation the quadratic values are incorrect. What I mean is that the values
resulting from the trend line are not in the same y range as the original
data from which the trendline was made. How can the same equation describe
two different graphs? Here is the data:
1950 1.05
1951 2.2
1952 3.735
1953 4.03
1954 3.07
1955 3.605
1956 2.68
1957 2.7
1958 2.905
1959 2.705
1960 2.995
1961 3.575
1962 2.625
1963 2.72
1964 3.035
1965 2.185
1966 2.59
1967 2.115
1968 3.025
1969 2.975
1970 2.59
1971 2.66
1972 1.115
1973 3.685
1974 2.12
1975 3.12
1976 2.235
1977 3.195
1978 2.075
1979 1.705
1980 2.39
1981 3.545
1982 1.985
1983 3.55
1984 3.38
1985 2.215
1986 3.4
1987 5.115
1988 3.13
1989 2.04
1990 3.675
1991 3.615
1992 2.725
1993 2.515
1994 3.08
1995 3.105
1996 1.85
1997 3.05
1998 5.635
1999 4.705
2000 3.74
2001 4.8
2002 3.585
2003 3.095
2004 2.985
2005 4.26

Any help would be greatly appreciated since this is really frustrating me.
The linear graph seems to make sense but the quadratic or cubic do not.
Thanks,
Katy

 

[Mike Middleton]

katy09 -

(1) Maybe you're not using enough significant digits for the coefficients.
One workaround is to increase the number of digits shown in the trendline
equation in the text box on the chart. You could click the trendline
equation text box once to select it, and then click the Increase Decimal
button repeatedly.

(2) A better way, usually, is to calculate the coefficients using worksheet
formulas. See
http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

(3) Another issue is to be sure that the X values are actually numbers and
not text. If even one of them is text (that might look like a number), Excel
uses 1,2,3,... for all of the X values when it fits the trendline equation.

- Mike
http://www.MikeMiddleton.com

 

[katy09]

Thanks for your help Mike. The significant digits was the problem!
Katy