+ Reply to Thread
Results 1 to 7 of 7

trendline issues in an x-y plot

  1. #1
    Registered User
    Join Date
    01-17-2017
    Location
    tucson
    MS-Off Ver
    not sure; windows 10
    Posts
    4

    trendline issues in an x-y plot

    i'm looking at ten years worth of data on a scatter plot. I ask for a sixth order polynomial to help make further decisions about the data. the equation is written on the plot, but is clearly not correct. does anyone know if the coefficients of the equation which generated the trendline are written somewhere accessible? does anyone know if one could define an output range to capture the trendline values? quite frustrated.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,196

    Re: trendline issues in an x-y plot

    How did you determine that the trendline coefficients are not correct? My experience is that they are usually correct (they tend to default to too few significant figures, but are otherwise correct) for the regression as given.

    If it is a simple matter of too few significant figures (very common for high order polynomials), simply format the trendline equation to display more digits (I like to use scientific notation like "0.00000000E+0", when I believe eight sig figs is sufficient).
    Polynomials, like all of the other chart trendline equation options, are "linear" (or linearizable) and the regression can be performed directly in the spreadsheet using the LINEST() function (help file here https://support.office.com/en-us/art...a-fa7abf772b6d which includes an example for a 3rd order polynomial before the examples section).
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,950

    Re: trendline issues in an x-y plot

    Please update your user profile with the current version of Excel that you are using (visible on the splash screen when you run up Excel). Thanks.

    I have closed the duplicate thread you started today - please continue here.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Registered User
    Join Date
    01-17-2017
    Location
    tucson
    MS-Off Ver
    not sure; windows 10
    Posts
    4

    Re: trendline issues in an x-y plot

    thanks ali. I realize I have some work to do on interaction. the only reason I tried again with the post today is the previous response regarding accessibility was not satisfactory. it was my mistake not to have followed up then.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,950

    Re: trendline issues in an x-y plot

    Mr Shorty gave an answer based on the information you gave, which was not particularly detailed. Providing a sample workbook showing what you are trying to work with would help.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,196

    Re: trendline issues in an x-y plot

    The LINEST() function is the easiest way to get trendline coefficients in the spreadsheet. If the Excel help file I linked to before is not helping you, maybe try reading through these other pages (found by putting "linest for polynomials" into my favorite internet search engine):
    http://www.agcross.com/2014/01/excel...nts-trendline/
    https://newtonexcelbach.com/2015/06/...-and-warnings/

    There are other pages among the results as well.

  7. #7
    Registered User
    Join Date
    01-17-2017
    Location
    tucson
    MS-Off Ver
    not sure; windows 10
    Posts
    4

    Re: trendline issues in an x-y plot

    thanks mrshorty. excellent references. sorry I was rude not to reply earlier. I've used linest() for linear analysis for some time now. I work alone, so have no one to chat with about these things. I see from your references that I may do precisely what I was hoping for all along. i'm excited to do better work more efficiently. thanks again

+ 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