Excel calculating totally wrong trend line equations

**exclnoob** · 07-10-2012, 11:16 AM

I have a set of data that I plotted using a scatter plot with straight lines in Excel 2010. I added a trend line, made it a 4th degree polynomial, and then used that equation to calculate the data that I used for the plot. I then plotted the calculated data vs. the original data, and while the trend line equations are the same (as they should be), the lines themselves look totally and completely different.

Take a look at the attached plot.. Excel calculates the trend lines as being the same, even though they obviously are not.

What gives?

**ben_hensel** · 07-10-2012, 11:24 AM

This would be much easier to diagnose with a copy of the spreadsheet rather than a picture of the chart.

**exclnoob** · 07-10-2012, 11:36 AM

Here you go

Thanks for your help.

**MrShorty** · 07-10-2012, 11:53 AM

It looks to me that, like a lot of people, you are using the rounded coefficients as they are currently displayed in the chart. In particular, the x^4 and x^3 coefficients are only to given to one significant figure, and when you use these coefficients rounded to 1 sig fig, you will get very different values than you should.

I would suggest trying to format the equation so it displays all digits (maybe a number format code of 0.0000000E+0 to get 8 sig figs). Either that or I would suggest using the LINEST function to generate the coefficients to their full precision in the spreadsheet.

**ChemistB** · 07-10-2012, 11:58 AM

The trendlines are not showing enough significant digits to give you an accurate picture. I suggest using LINEST.

First, create columns for x^2, x^3, and x^4. (I deleted your actual x in col B and used A3:D10 for all the x components)
Then select 5 cells across columns (I used A28:E28) and enter

=LINEST($A$17:$A$24,$A$3:$D$10) Enter with CNTRL SHFT ENTER to make it an array formula. Your coefficients will appear in the cells.
Your formula for the first y value would then be

=$A$28*A3^4+$B$28*A3^3+$C$28*A3^2+$D$28*A3+$E$28
Make sense? See attachment

**jpr73** · 08-08-2012, 05:12 AM

Hi,
In continuation of the posts and McShorty ChemistB, I enclose a solution using LINEST which allows to calculate any value
Hope this help
Best regards

**derik111** · 09-27-2012, 06:03 AM

On MSFT site, it is said that the number formatting of the polynomial function in the graph is not precise enough and that we can correct this by changing the number of decimals. For example in the calculation_trendline_jpr73.xlsm, the function was : y = 1E-07x4 - 0.0001x3 + 0.0587x2 - 10.43x + 867.75
but in reality it is :
y = 0.0000000999999999997314x4 - 0.000100000000000026x3 + 0.0586999999998742x2 - 10.4299999999427x + 867.749999993586
Now what I don't understand is why when we use LINEST we obtain this:
y= 0.000000137525200690634x4 - 0.000146864975803026x3 + 0.0586512659056951x2 -10.4304873662204x +
867.747984421906

I mean it is close but it is not the same. What explains the difference ?

Thanks in advance

**arlu1201** · 09-27-2012, 06:22 AM

derik111,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.