Trendline equation explanation

[ubh_65]

This is my first post in this forum.

I have developed a trendline for a data series (2 items X & Y) with the intention of using in a program.
I wish to get the value for a given X values.

The data series is :
x & f(x)
400 0.000250
500 0.000180
600 0.000125
700 0.000088
800 0.000070
900 0.000060
1000 0.000048
1500 0.000028
2000 0.000015

The equation is :
y = 5.5128E-08x5 - 1.3692E-06x4 + 1.1649E-05x3 - 3.4550E-05x2 - 2.8124E-05x + 3.0217E-04

For testing purpose, when I give the x value of 800, I expect to get the Y value equal to 0.00007 whereas I get a value equal to 19256300.7763.

Why is there such a big difference?

Request help.

Regards

 

[Andrew Poulsom]

What formula did you use?

 

[GlennUK]

Where did you get that trendline equation?

If I do a trendline on that data I get:
y = 7E-20x5 - 1E-16x4 - 7E-13x3 + 2E-09x2 - 2E-06x + 0.0008

 

[Andrew Poulsom]

If you want to use a fifth order polynomial to solve for 800 with your data:

=(INDEX(LINEST(B2:B10,A2:A10^{1,2,3,4,5}),1)*800^5)+(INDEX(LINEST(B2:B10,A2:A10^{1,2,3,4,5}),1,2)*800^4)+(INDEX(LINEST(B2:B10,A2:A10^{1,2,3,4,5}),1,3)*800^3)+(INDEX(LINEST(B2:B10,A2:A10^{1,2,3,4,5}),1,4)*800^2)+(INDEX(LINEST(B2:B10,A2:A10^{1,2,3,4,5}),1,5)*800^1)+INDEX(LINEST(B2:B10,A2:A10^{1,2,3,4,5}),1,6)

 

[Lori_M]

Change the chart type from Line to XY, then you should see the correct equation as in GlennUK's.

However, polynomial looks like a poor choice of fit for this data. Try using a power fit instead which gives
y = 7.8064x^-1.729, R² = 0.9955

for x = 800, y = 0.000074...