Bug in excel 2003 - trendline equation?

[FVe]

Hi,

I'm trying to get an equation of a table of data.

When I enter the data, make a graph of it, I get a nice sinus-kind-of-line.
Therefor I would like to have fourth order equation.

The line of data looks very good (125 data points).
Then I add a trendline (4th order) that looks like a very nice fit.
But when I set the properties of the trendline to "show equation in graph"
the a wrong equation is displayed.

After a lot of fiddling with data, I noticed that the shown equation has
wrong contstands showing.

E.g.:
The shown eqaution = A + B*X + C*X^2 + D*X^3 + E*X^4
In fact the correct equation = (A/10^0) + (B*X/10^1) + (C*X^2/10^2) +
(D*X^3/10^3) + (E*X^4/10^4)

Anyway, now I know where the economic crisis is coming from..... bankers
using the excel formula's for future analysis

Does anyone know if there is a solution for this problem?
Or is there something I do wrong?

Thanks,
Frank

Calculated equation:
y = 7,89E-08x4 - 1,85E-03x3 + 6,86E-01x2 + 4,59E+01x - 2,35E+02

The correct equation (as it should be):
y = 7,89E-12x4 - 1,85E-06x3 + 6,86E-03x2 + 4,59E+00x - 2,35E+02

Table of data:
X Y
0 0
10 10
20 30
30 56
40 87
50 121
60 160
70 201
80 245
90 293
100 342
110 395
120 449
130 506
140 565
150 625
160 688
170 753
180 819
190 887
200 957
210 1028
220 1101
230 1176
240 1252
250 1329
260 1408
270 1488
280 1569
290 1652
300 1736
310 1821
320 1907
330 1995
340 2084
350 2173
360 2264
370 2356
380 2449
390 2543
400 2638
410 2733
420 2830
430 2928
440 3027
450 3126
460 3226
470 3328
480 3430
490 3532
500 3636
510 3741
520 3846
530 3952
540 4058
550 4166
560 4274
570 4383
580 4492
590 4602
600 4713
610 4824
620 4936
630 5049
640 5162
650 5276
660 5390
670 5505
680 5620
690 5736
700 5853
710 5970
720 6087
730 6205
740 6324
750 6443
760 6562
770 6682
780 6802
790 6923
800 7044
810 7165
820 7287
830 7409
840 7532
850 7655
860 7778
870 7901
880 8025
890 8150
900 8274
910 8399
920 8524
930 8650
940 8776
950 8901
960 9028
970 9154
980 9281
990 9408
1000 9535
1010 9662
1020 9790
1030 9918
1040 10046
1050 10174
1060 10302
1070 10430
1080 10559
1090 10688
1100 10817
1110 10946
1120 11075
1130 11204
1140 11333
1150 11462
1160 11592
1170 11721
1180 11851
1190 11981
1200 12110
1210 12240
1220 12370
1230 12500
1240 12629
1250 12759
1260 12889
1270 13019
1280 13148
1290 13278
1300 13408
1310 13537
1320 13667
1330 13796
1340 13926
1350 14055
1360 14184
1370 14313
1380 14442
1390 14571
1400 14700
1410 14829
1420 14957
1430 15086
1440 15214
1450 15342
1460 15470
1470 15598
1480 15725
1490 15852
1500 15979
1510 16106
1520 16233
1530 16359
1540 16485
1550 16611
1560 16737
1570 16862
1580 16987
1590 17112
1600 17236
1610 17361
1620 17484
1630 17608
1640 17731
1650 17854
1660 17976
1670 18098
1680 18220
1690 18341
1700 18462
1710 18583
1720 18703
1730 18822
1740 18942
1750 19060
1760 19178
1770 19296
1780 19414
1790 19530
1800 19647
1810 19762
1820 19878
1830 19992
1840 20106
1850 20220
1860 20333
1870 20445
1880 20557
1890 20668
1900 20779
1910 20888
1920 20998
1930 21106
1940 21214
1950 21321
1960 21427
1970 21533
1980 21638
1990 21742
2000 21845
2010 21948
2020 22049
2030 22150
2040 22250
2050 22349
2060 22448
2070 22545
2080 22641
2090 22737
2100 22831
2110 22925
2120 23017
2130 23109
2140 23199
2150 23288
2160 23377
2170 23464
2180 23550
2190 23634
2200 23718
2210 23800
2220 23881
2230 23960
2240 24038
2250 24115
2260 24191
2270 24264
2280 24337
2290 24408
2300 24477
2310 24544
2320 24610
2330 24674
2340 24736
2350 24796
2360 24854
2370 24909
2380 24963
2390 25014
2400 25063
2410 25109
2420 25152
2430 25192
2440 25229
2450 25262
2460 25291
2470 25315
2480 25332

 

[Shane Devenshire]

Hi,

When you say the the correct formula for a fourth degree polynomial is

(A/10^0) + (B*X/10^1) + (C*X^2/10^2) +
(D*X^3/10^3) + (E*X^4/10^4)

If you do a Google on fourth degree polynomial - I don't think you will find
any sites that show your formula, instead they match what Excel seems to be
using. Here are a few:

http://en.wikipedia.org/wiki/Quartic_equation
http://www.karlscalculus.org/quartic.html
http://mathworld.wolfram.com/QuarticEquation.html

I don't know about the first two, but the last is a well respected math site.

 

[Jerry W. Lewis]

Actually the chart trendline did a very accurate job of estimating the
coefficients for the problem that you asked it to (which was quite different
than the problem that you intended).

The (misleadingly named) "Line" chart has nothing to do with whether you
want to draw a line through the data or not. Instead, it indicates to Excel
that your x-axis refers to categories, not numbers. Why it would then offer
to do a numeric regression is beyond me, but if you ask it to (as you
obviously did from your reported equation), then it will use the numbers
1,2,3,... to represent the categories in that regression, even though you
supplied category labels of 0,10,20,...

If you change the chart type to "XY (Scatter)", then Excel will understand
that you intent your x column to be interpreted as numeric values for the
regression, instead of category labels. The resulting chart trendline will
be accurate to 9+ figures (if you choose to display that many). LINEST will
give you 12+ figure accuracy for the same problem. The loss of precision in
these two cases reflects the numeric difficulty (condition number of 4E27) of
fitting a quartic equation over so narrow a range of x values. You should
always beware when fitting high order polynomials, because you can easily go
beyond what can be successfully fitted with the available precision. SAS
PROC GLM and LINEST in Excel version prior to 2003 cannot accurately estimate
the quartic term at all, and hence lose accuracy on other terms as well.

Use of LINEST for fitting polynomials is discussed at
http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm

Jerry

 

[FVe]

Hi,

When you say the the correct formula for a fourth degree polynomial is

(A/10^0) + (B*X/10^1) + (C*X^2/10^2) +
(D*X^3/10^3) + (E*X^4/10^4)

Hi Shane,

thanks for your reply.

The reason I wrote the equation this way, is to show the difference between
the shown one by excel and the correct values as they should be in my
example.
It does show only with what amount I have to divide the constants with, for
getting the correct values.
It is nice to remember divide for example the "C"-value by 100 (C/10^2 for
x^2 and so on).

I didn't intend to prove to be smarter then wolfram'mathworld

The solution is now solved, thanks

Frank

 

[FVe]

Actually the chart trendline did a very accurate job of estimating the
coefficients for the problem that you asked it to (which was quite
different
than the problem that you intended).

The (misleadingly named) "Line" chart has nothing to do with whether you
want to draw a line through the data or not. Instead, it indicates to
Excel
that your x-axis refers to categories, not numbers. Why it would then
offer
to do a numeric regression is beyond me, but if you ask it to (as you
obviously did from your reported equation), then it will use the numbers
1,2,3,... to represent the categories in that regression, even though you
supplied category labels of 0,10,20,...

Wow, that is tricky!

I changed the chard type to scatter and now the shown equation is correct!

With the new graph the calculated equation has an error les then 0,25% at
all my tables (20) and within the range I'm interested in, so that is very
accurate.

For some strange reason I get a smile from ear-to-ear when I look at the
result....

You should
always beware when fitting high order polynomials, because you can easily
go
beyond what can be successfully fitted with the available precision.

I absolutely agree, that's why I added a "calculated" colum in my sheet next
to the "measured" values and found out the big error in the calculation. But
I don't think a lot of people will check their results that way and take the
answer as-is.


thanks very much for your big help,

Frank

 

[Jerry W. Lewis]

You're welcome

Jerry

:

....