Bug in Excel Trend Lines 1

[Samiran]

Has anybody encountered a bug in the trend lines which Excel generates from regression? The equation of the trend line did not give me the expected values. I was working on exponential and polynomial type of lines. I had to adjust the co-efficients of the equations to get somewhat better results.

Thanks.

Samiran

 

[jproj]

Samiran:

The more data points you have, the more the trendline (and its equation) will represent the data you have, especially if your data fits a polonomial expression.

I've had the same experience. I'll have a bunch of data that the trendline represents really well in the middle, but is in >90% error on either end point of the data.

If you are trying to fit an equation to data so you can calculate "y" by entering "x" into a cell in excel, it may be helpful to split your data into sections and fit trendlines to each section instead of trying to get one equation to fit the entire data range.

Example:

for data of y = 1-200, x = 30-600

Instead of entering all data from y=1 (x=30) to y=200 (x=600), enter y=1 to y=100 in one chart and y=101 to y=200 in another chart and fit trendlines to each graph.

Good luck!

jproj

 

[Samiran]

DEAR JPROJ,

The idea you've suggested is actually not bad - to have diff trend line s for diff sets of data. This only shows one should always try to query results before walking away happily with the solution offered by any readymade tool.

Thanks again.

Samiran

 

[ailsas]

Another thing to try is to have the trendline equation displayed with more decimal places. Select the label with the equation written on it and choose "Format Data Labels, Number", then choose a larger number of decimal places. This quite often helps.

Ailsa

 

[Miper]

Sometimes the accuracy of the trend line is improved if you change the order of the data tables. In other words, plot X vs Y instead of Y vs X. I have used this approach to get the curve to fit the data better at the ends of the curve. It doesn't seem mathematically necessary to manipulate the data this way in order to get the trend line method in EXCEL to work, but it helps.

 

I have just followed Ailsa's advice and increased the number of decimal places displayed for the equation constants from 1 to 6 (ie from 1.0E5 to 1.000000E5). This totally eliminated a 90% error at the extreme end of the data range. Good? I think so!