Using the Trend Line Equation

**ubh_65** · 07-28-2011, 08:29 AM

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

**zbor** · 07-28-2011, 08:36 AM

According to your data can you try:

=7.8064*POWER(A1, -1.729)

**ubh_65** · 07-28-2011, 09:12 AM

Wow, such a quick response.
Thanks.

I am sorry, I do not understand , can you please explain.
I shall be very grateful.

I forgot that the equation derived, as can be seen, is a 5th order polynomial.

**zbor** · 07-28-2011, 09:39 AM

I don't know where you got your trendline but it's wrong:

**MrShorty** · 07-28-2011, 09:40 AM

Double check your regression parameters. I get a very different set of coefficients: 6.7e-20, -1.1e-16, -6.7e-13, 2.14e-9, -2.2e-6, 8.4e-4.

**ubh_65** · 07-28-2011, 09:50 AM

I do not have even the surface knowledge of Regression but the equation is the same I have posted earlier.

I have attached the excel sheet for your checking please.
Thanks for all the feedback.

**shg** · 07-28-2011, 10:37 AM

You would be better off using an exponential or power-series fit to the data.

Please take a few minutes reading the forum rules about cross-posting, and then post links to other forums wher you have posted the same question.

**ubh_65** · 07-28-2011, 09:29 PM

OK. I did not see the rules. Sorry about that.

Why are the constants, I am having are different?

**MrShorty** · 07-29-2011, 10:38 AM

Why are the constants, I am having are different?

That is the $64 question. Any number of reasons, including
1) data entry error: make sure the data has been entered correctly into the spreadsheet/program/routine that is doing the regression.
2) equation mismatch: you've told us it is a 5th order polynomial. Make sure that is what the regression routine has been "told" as well.
3) general regression error: the regression routine is failing to converge on the correct solution. Debugging this will depend on what regression routine/program you are using to generate the coefficients.