Trendline equation not giving correct results

  • Thread starter Thread starter Sudhanshu
  • Start date Start date
S

Sudhanshu

I was trying to form a trendline with following data :
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 trendline (Polynominal
with order 6) alongwith 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

The trendline 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 are entirley
different from the data as shown in Y above. Following is
the output of the equation for various values of X

Y1 X1
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

When I plotted above data by adding another series it
gives entirely different curve.

I am unable to understand above.

Anybody can help me understanding above and how to rectify
above problem.

Thanks
Sudhanshu
 
Double-click the equation of the polynomial on the graph. From the
Number tab, select a format of 'Number' and set the number of decimals
to 15.

That said, consider plotting the data first. You will see that the use
of a sixth order polynomial is unwarranted, and that a graph is a
probably best described by two straight lines.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Sudhanshu -

Did you notice the lack of significant digits in the trendline equation?
6E-07? Barely one significant digit, with almost a ten percent error
(6±0.5).

First of all, put the X to the left of Y. This matches the way Excel
naturally assigns X and Y to a series, so you won't have to fix it. Then
make the chart, add the trendline, and double click on the trendline.
Click on the Number tab, click on Scientific, and select 14 decimal
places. Now you have enough precision to use the coefficients to compute
your own values.

If you don't want to do all the copy-pasting required to get these
coefficients into the worksheet, Dave Braden has posted code to do this
for you:
http://groups.google.com/[email protected]

If you want 2¢ more from me, keep reading. It looks like your data is a
sloping curve that hits a plateau at Y=1.128. If I fit a 2nd order
polynomial to the points with Y<0.128, I get a better R-squared. In
general, higher order fits are only good for interpolation (IMHO);
nobody can convince me there's any physical significance to higher order
terms than 2nd order. In fact, a linear fit of these points gives me a
better R-squared than the 6th order fit of the entire data set, although
you can see a slight curvature by eye. The apparent need for excess
orders in the poly fit is required to account for the discontinuity in
the data, a transition from one mechanism of variation to another.

- Jon
 
-----Original Message-----
I was trying to form a trendline with following data :
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 trendline (Polynominal
with order 6) alongwith 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

The trendline 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 are entirley
different from the data as shown in Y above. Following is
the output of the equation for various values of X

Y1 X1
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

When I plotted above data by adding another series it
gives entirely different curve.

I am unable to understand above.

Anybody can help me understanding above and how to rectify
above problem.

Thanks
Sudhanshu


right click the equation text box on the chart and this
Click to expand...
gives "format data label" choose "number" and select a
high value of decimal points (I use 10) this will give you
a much more accurate curve. If it isn't accurate enough go
for more decimal places. If it's really complicated and a
sixth order poly isn't enough cut the graph into sections
each with its own polynomial and use =if (between one
value and another one poly ; the other).

Mike G
 
You should consider whether you might be seriously overfitting the data.
There is essentially no improvement in the fit over a cubic polynomial.

Unless Y is known to more decimal places, you would do even better with
a piecwise linear equation joined between -5 and -6. The higher order
polynomials are trying with limited success to model that sudden shift
from essentially linear with a slope of -0.0058... to linear with a
slope of 0.

Jerry
 
Back
Top