What is the trap in excel Chart Trendline(Regression)? 4

[IJR]

My dear pals

I have the following x and y data

x y
1.00 0,033
1.10 0,040
1,20 0,045
1,30 0,050
1,50 0,059
1,75 0,070
2,00 0,083

After drawing a chart carefully I notice that a good 3rd order polynomial really fits the curve with R squared of about 0.9989(what a fit!) and excel returns a polynomial y =0,0002x3 -0,0025x2 + 0,0134x + 0,0218.The intercept 0,0218 really satisfies my visual inspection as well as the P-curve (the trendline).

Problem : When I use the equation to regenerate y values using the same x-values only the first y becomes correct, the rest are very very less than reality.

Am I having an interpretation or processing or both problems?

Respects
IJR

 

[Latexman]

I got y = 0.0287x3 - 0.1329x2 + 0.2475x - 0.1101. R^2 = 0.9998. It works on all the range.

The data you showed had mixed mode decimal points (periods and commas). I don't know if that's the problem.



Good luck,
Latexman

 

[IJR]

Latexman

Thanx for your fast response.

My decimal points and commas above should be taken as decimal points ie 0,10 is to mean 0.10. Did you fit my data with that reasoning?

Thanx anyway for checking

respects
ijr

 

[Latexman]

Yes, I took both as decimal points.

Good luck,
Latexman

 

[ivymike]

tip: make sure that you display enough significant figures in the trendline (format|number) to accurately reproduce the data.

 

[GregLocock]

I am told that the equation for the trend line that is displayed can be innaccurate. I have not seen this myself.

Cheers

Greg Locock

 

[IJR]

Dear Pals

Thanx to Latexman for telling me something is wrong with me.

THE TRAP: CAREFULLY SELECT THE "TYPE OF CHART" TO USE BEFORE DOING INTELLIGENT WORK LIKE TRENDLINE REGRESSION

or at least that is what I have done to correct my trendline. Wrongly(for the usual routine of using charts for visual appeal only) I used curve type charts.

Correcting the chart type to "xy scatter with data connected by lines" got me the correct trendline.

Someone might want to elaborate on this

Respects
IJR

 

[TrevorP]

I have found that on 4th order polynomials and above, the equation displayed is given to only one or two significant figures. This means that if you use the data, you get a curve that does not fit the trend, due to rounding errors. I've not found this problem with lower order polynomials.

In this case, I found that if you format the number of the equation to the maximum number of decimal places, rather than 'general', then this will often give more information.

 

[mattis]

You can get strange numbers from the curve fit if you do a trendline when one or both axis are in a logarithmic scale.

//nisse

 

[pmover]

IJR,

Please look at the following posting thread770-23296 regarding the linest function and its capabilities.

While i do use the trendline feature, I also use the linest function to determine the constants as well.

i hope this helps.
good luck!
-pmover

 

[BML]

Also beware that the typical Excel chart assumes the X values are in buckets - that is, they are discrete and not ordinal. (So X1 is 1, X2 is 2, even if X2 is really 1.5) I've had to specifically tell Excel that the data is time series, then change the number format on the chart. But this usually only works with integer data.

Also don't forget that regressions are only good within the bounds of the orignial X's.

Good luck!

 

[IJR]

BML

That is why you have to be careful when you choose your chart type. XY scatter takes X and Y as they are and you can see them right on screen the way they are spaced apart

respects
ijr

 

[cummings54]

If you use the function LINEST you can return polynomial coefficients with the full 15 digit precision available in Excel. For example consider that the X values are located in cells a1:a7 and the Y values are in cells B1:B7, then
select a10:d10 and enter the array formula

=LINEST(B1:B7,A1:A7^{1,2,3},TRUE,FALSE)

in a10 using the key combination Ctrl-Shift-Enter.

The order-3 best-fit polynomial coefficients are in A1010

If you want a fifth order polynomial

=LINEST(B1:B7,A1:A7^{1,2,3,4,5},TRUE,FALSE)
substituting A10:F10 for A10:d10