+ Reply to Thread
Results 1 to 7 of 7

How to calculate trend line growth rate (as an annual percentage growth rate)

  1. #1
    Registered User
    Join Date
    02-13-2014
    Location
    2829
    MS-Off Ver
    Excel 2010
    Posts
    6

    How to calculate trend line growth rate (as an annual percentage growth rate)

    From a chart in Excel I need to automatically calculate what the annual percentage growth rate is of a trend line. Does anyone know how to automate this in Excel? I've attached a sample so you can see what I'm trying to accomplish. Thanks!

    Rob
    Attached Files Attached Files

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

    Re: How to calculate trend line growth rate (as an annual percentage growth rate)

    IMO, the best way to automate this sort of thing is to perform the regression directly in the spreadsheet and skip using the chart trendline feature. This is perhaps most easily accomplished using the =LINEST() function http://office.microsoft.com/en-us/ex...in=HA010277524
    You may also want to be aware of the LOGEST() function (http://office.microsoft.com/en-us/ma...829.aspx?CTT=1), though it is really just a specific application of the same regression algorithms LINEST() uses.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    02-13-2014
    Location
    2829
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How to calculate trend line growth rate (as an annual percentage growth rate)

    the LINEST function is not giving me the growth rate of the trend line - or - I do not know how to use the number it is giving me to get the growth rate?

  4. #4
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007
    Posts
    263

    Re: How to calculate trend line growth rate (as an annual percentage growth rate)

    your dates are string, so i made datevalues of it.
    Then the linear regression values are in B29:D29, on daily base, so multiply with 365 for a year
    exponential ?

    modified attachment
    Attached Files Attached Files
    Last edited by bsalv; 02-13-2014 at 03:50 PM.
    Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

  5. #5
    Registered User
    Join Date
    02-13-2014
    Location
    2829
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How to calculate trend line growth rate (as an annual percentage growth rate)

    Quote Originally Posted by bsalv View Post
    your dates are string, so i made datevalues of it.
    Then the linear regression values are in B29:D29, on daily base, so multiply with 365 for a year
    exponential ?

    modified attachment
    thank you!!!!!

  6. #6
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007
    Posts
    263

    Re: How to calculate trend line growth rate (as an annual percentage growth rate)

    Did you found a solution for the exponential part of the question ?

  7. #7
    Registered User
    Join Date
    02-13-2014
    Location
    2829
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How to calculate trend line growth rate (as an annual percentage growth rate)

    you're a genius! i've never seen this formula before:
    =DATE(2000+RIGHT(B1,2),LEFT(B1,2),MID(B1,4,2))
    got it. thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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