+ Reply to Thread
Results 1 to 9 of 9

Using the Trend Line Equation

Hybrid View

ubh_65 Using the Trend Line Equation 07-28-2011, 08:29 AM
zbor Re: Using the Trend Line... 07-28-2011, 08:36 AM
ubh_65 Re: Using the Trend Line... 07-28-2011, 09:12 AM
zbor Re: Using the Trend Line... 07-28-2011, 09:39 AM
MrShorty Re: Using the Trend Line... 07-28-2011, 09:40 AM
ubh_65 Re: Using the Trend Line... 07-28-2011, 09:50 AM
shg Re: Using the Trend Line... 07-28-2011, 10:37 AM
ubh_65 Re: Using the Trend Line... 07-28-2011, 09:29 PM
MrShorty Re: Using the Trend Line... 07-29-2011, 10:38 AM
  1. #1
    Registered User
    Join Date
    07-28-2011
    Location
    UAE
    MS-Off Ver
    Excel 2003
    Posts
    4

    Using the Trend Line Equation

    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

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,038

    Re: Using the Trend Line Equation

    According to your data can you try:

    =7.8064*POWER(A1, -1.729)
    Never use Merged Cells in Excel

  3. #3
    Registered User
    Join Date
    07-28-2011
    Location
    UAE
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Using the Trend Line Equation

    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.
    Last edited by ubh_65; 07-28-2011 at 09:15 AM.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,038

    Re: Using the Trend Line Equation

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

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,385

    Re: Using the Trend Line Equation

    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.

  6. #6
    Registered User
    Join Date
    07-28-2011
    Location
    UAE
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Using the Trend Line Equation

    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.
    Attached Files Attached Files

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Using the Trend Line Equation

    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.
    Last edited by shg; 07-28-2011 at 04:33 PM.
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Registered User
    Join Date
    07-28-2011
    Location
    UAE
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Using the Trend Line Equation

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

    Why are the constants, I am having are different?

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,385

    Re: Using the Trend Line Equation

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1