|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 26th, 2009, 10:03 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
trend line equations
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 
|
|
#2
August 27th, 2009, 02:09 AM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
trend line equations
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...line_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" wrote in message ... 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
|
|
#3
August 27th, 2009, 06:57 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
trend line equations
Thanks for your help Mike. The significant digits was the problem!
Katy "Mike Middleton" wrote: 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...line_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" wrote in message ... 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
