S
sudhanshu
I was trying to form a trend line in a chart with following input data
in Excel 2000:
Y X
1.128 -20
1.128 -15
1.128 -10
1.128 -6
1.111 -5
1.084 0
1.056 5
1.0276 10
1 15
0.971 20
0.944 25
0.9144 30
0.884 35
0.8524 40
0.82 45
0.788 50
After plotting the chart, I added trend line (Polynomial with order 6,
set intercept = 0) along with its equation, which read as below:
y = 6E-07x6 - 3E-05x5 + 0.0006x4 - 0.0057x3 + 0.0215x2 - 0.0318x +
1.1429 and R2 = 0.9998 ------ (Eq. 1)
Though the trend line in the chart neatly fitted over the curve,
however, when I used above equation and fed in same values of X as
shown above, the computed results were found to be entirely different
from the data as shown in Y above. Similar was the case when I chose
Polynomial of order 2, set intercept = 0.
y = -0.0005x2 + 0.0028x + 1.0186 and R2 = 0.9987--------- (Eq. 2)
Following is the output of the equation for various values of X
Y1 X1 (Output from Eq. 1)
286.379 -20
85.6855 -15
18.9109 -10
4.37777 -6
3.03003 -5
1.1429 0
1.09953 5
0.8749 10
0.69402 15
1.9069 20
12.6135 25
60.0389 30
209.658 35
591.071 40
1430.63 45
3090.8 50
Y2 X2 (Output from Eq. 2)
0.7626 -20
0.8641 -15
0.9406 -10
0.9921 -5
1.0186 0
1.0201 5
0.9966 10
0.9481 15
0.8746 20
0.7761 25
0.6526 30
0.5041 35
0.3306 40
0.1321 45
-0.0914 50
When I plotted above data (results of both the equations) by adding
additional series it gives entirely different curves way off the curve
plotted by using original data.
I am unable to understand above.
Anybody can help me understanding above and how to rectify above
problem, so that equation of trend line results in values of Y, which
are very near to the original data fed in and the curve plotted using
results of equation matches with the original data.
Thanks
Sudhanshu
in Excel 2000:
Y X
1.128 -20
1.128 -15
1.128 -10
1.128 -6
1.111 -5
1.084 0
1.056 5
1.0276 10
1 15
0.971 20
0.944 25
0.9144 30
0.884 35
0.8524 40
0.82 45
0.788 50
After plotting the chart, I added trend line (Polynomial with order 6,
set intercept = 0) along with its equation, which read as below:
y = 6E-07x6 - 3E-05x5 + 0.0006x4 - 0.0057x3 + 0.0215x2 - 0.0318x +
1.1429 and R2 = 0.9998 ------ (Eq. 1)
Though the trend line in the chart neatly fitted over the curve,
however, when I used above equation and fed in same values of X as
shown above, the computed results were found to be entirely different
from the data as shown in Y above. Similar was the case when I chose
Polynomial of order 2, set intercept = 0.
y = -0.0005x2 + 0.0028x + 1.0186 and R2 = 0.9987--------- (Eq. 2)
Following is the output of the equation for various values of X
Y1 X1 (Output from Eq. 1)
286.379 -20
85.6855 -15
18.9109 -10
4.37777 -6
3.03003 -5
1.1429 0
1.09953 5
0.8749 10
0.69402 15
1.9069 20
12.6135 25
60.0389 30
209.658 35
591.071 40
1430.63 45
3090.8 50
Y2 X2 (Output from Eq. 2)
0.7626 -20
0.8641 -15
0.9406 -10
0.9921 -5
1.0186 0
1.0201 5
0.9966 10
0.9481 15
0.8746 20
0.7761 25
0.6526 30
0.5041 35
0.3306 40
0.1321 45
-0.0914 50
When I plotted above data (results of both the equations) by adding
additional series it gives entirely different curves way off the curve
plotted by using original data.
I am unable to understand above.
Anybody can help me understanding above and how to rectify above
problem, so that equation of trend line results in values of Y, which
are very near to the original data fed in and the curve plotted using
results of equation matches with the original data.
Thanks
Sudhanshu