+ Reply to Thread
Results 1 to 9 of 9

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
    15,802

    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 Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    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 Expert
    Join Date
    01-25-2011
    Location
    Belgium, Alveringem
    MS-Off Ver
    Excel 2003, 2007, 365
    Posts
    1,418

    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!

  8. #8
    Registered User
    Join Date
    02-01-2020
    Location
    india
    MS-Off Ver
    2007
    Posts
    1

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

    cant download the excel sheet

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

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

    I have no problems downloading the file?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 06-18-2012, 04:08 AM
  2. Average Annual Growth Rate
    By droddis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-08-2008, 01:22 PM
  3. [SOLVED] Compound Annual Growth Rate
    By Stash in forum Excel General
    Replies: 2
    Last Post: 03-30-2005, 03:06 PM
  4. [SOLVED] What formula do I use to calculate compound annual growth rate (C.
    By pjbrien in forum Excel General
    Replies: 0
    Last Post: 03-23-2005, 05:06 PM
  5. Compound annual growth rate [CAGR]
    By Paul in forum Excel General
    Replies: 2
    Last Post: 03-17-2005, 08:06 PM

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