Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    When I make a scatterplot in Excel 2003, and choose Add Trendline, I have the option to show the formula in the chart. I would like to use this formula. The problem is, the only way I know how to do this is to manually type the components of the formula into some cells and manually create the formula. This is sometimes ok, but when the data underlying the scattreplot change, so does the formula. Is there a way I can make a derived trendline formula which is dynamic?

    I'd be grateful for any help.

    regards

    Ken

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You didn't say what kind of trendline but all the formulas are shown here.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post
    Thank you Rory!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In addition to the slope/intercept for the line, you can keep them "generic" and all use the LINEST by having:
    m: =INDEX(LINEST(y,x),1)
    b: =INDEX(LINEST(y,x),1,2)

    For all the equations, the Correlation Coefficient (Rē) listed with trendline can be calculated by Indexing on row 3, Column1:
    Linear: =INDEX(LINEST(y,x,TRUE,TRUE),3,1)
    Logarithmic: =INDEX(LINEST(y,ln(x),TRUE,TRUE),3,1)
    Power: =INDEX(LINEST(ln(y),ln(x),TRUE,TRUE),3,1)
    Exponential: =INDEX(LINEST(ln(y),ln(x),TRUE,TRUE),3,1)
    2nd Order: =INDEX(LINEST(y,x^{1,2},TRUE,TRUE),3,1)
    3rd Order: =INDEX(LINEST(y,x^{1,2,3},TRUE,TRUE),3,1)

    LINEST also contains other statistics (errors of the constants, F values, etc) by using the proper row and column of the index. The setup of the LINEST output array is in the help...

    Steve

Posting Permissions

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