Search code examples
excelmathgraphpolynomial-mathtrendline

Excel Equation of line not correct

I'm hoping someone will be able to tell me why the equation that Excel generated is not giving the correct results as it is graphed correctly.

I have some X and Y points that I will list below. I plotted those points in Excel and then plotted the trend line, and had it show me the equation of the trendline. When I take the equation and then plug in the X values I get very different answers back.

X and Y Values

X     Y
0     3
3     2
5    1.4
7    1
10   0.5
18   0.1

When I set the intercept to 3, the equation of the trendline is y = 0.0088x5 - 0.1457x4 + 0.8753x3 - 2.224x2 + 1.4798x + 3

Screenshot of Excel window with equation

Excel screen shot

Any help is greatly appreciated.

Solution

  • I suspect you didn't set up your graph correctly.

    • Select a single cell in your table
    • Insert/Scatter (and decide which you want with regard to markers, etc)
    • Select the line and add Trendline
    • Set you parameters for the trendline
    • If you want to get the formula for the trendline from the "show formula" option, be sure to format the trendline label to be numeric with 15 decimals. Otherwise the equation will certainly not work, even if it appears to be correct.

    enter image description here

    Note that you can obtain the formula directly using the LINEST worksheet function.

    =LINEST(Y,X^{1,2,3,4,5}) returns the array:    

  {0.0000399230399230442,-0.00152188552188569,0.0192991822991846,-0.0840134680134806,-0.217128427128402,2.99999999999999}

    The last value in the array is the y-intercept The slight differences are due to the use of different algorithms for the two methods.