+ Reply to Thread
Results 1 to 2 of 2

How to find optimal data range to fit exponential trendline?

  1. #1
    Registered User
    Join Date
    06-13-2018
    Location
    Denmark
    MS-Off Ver
    Office 365 for Mac
    Posts
    1

    How to find optimal data range to fit exponential trendline?

    Hi, Excel pros!

    I am looking through various datasets of Google Trends to find the most optimal data range that fits an exponential trend line. Do you know of an automated way to identify the most fitting data range in a graph (i.e. with the trend line R2 value closest to 1)?

    Currently, I have to guess and try out various data ranges manually and see which ones provide the highest R2-value.

    It's a simple x-y graph. X-axis consists of dates in monthly intervals, Y-axis of search volume in relative terms for the given keyword.

    Here's a photo of what it looks like for the search term "chatbot":

    Skærmbillede 2018-06-13 kl. 11.30.11.png

    As you can see, the R2 value is 0,84275 for the selected interval (the range of the entire dataset goes further back). I selected the interval simply by roughly guessing where the exponential trend is most present.

    What I am looking for is a way of determining the data range that provides the highest R2 - in other words, the part of the data that resembles an exponential trend the most.

    Do you know of any way to do this systematically, so I don't have to guess my way through?

    Thank you in advance.

    Elías

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

    Re: How to find optimal data range to fit exponential trendline?

    It is difficult to be specific with only the chart presented, because I would not use the chart at all to do this (except maybe as a visualization of the final solution found). Here's what I would probably try first (note that I am assuming the data are arranged in columns, not rows. Adjust as needed):

    0) Recall that exponential regression is really just simple linear regression on the transformation of the data LN(y)=mx+b, so add a column to compute LN(y_value).
    1) Enter row and height parameters for an OFFSET() function (https://support.office.com/en-us/art...e-b4d906d11b66 ). The OFFSET() function will be used to select the range to test from the data set.
    2) Nest the OFFSET() function inside of an RSQ() function (https://support.office.com/en-us/art...d-a8364f2be08f ). This will compute the R2 value for the chosen subset of data. =RSQ(OFFSET(first_known_y_cell,row_offset_cell,0,height_cell,1).
    3a) Try Solver to see if Solver can find the optimum. This is the kind of problem that Solver doesn't always do well with, but, if it works, it should be fairly easy. Set up Solver to (A) Set Target Cell -- RSQ() cell (B) to a maximum (C) by changing: the row offset and height cells (D) subject to the constraints that row offset and height are integers greater than 0 and height cell is larger than whatever minimum you think it should use and (E) using an Evolutionary algorithm. Let Solver try to solve this.
    3b) If Solver fails or takes a very long time (because it probably really just has to try every possible subset of the data and it doesn't always do well with problems requiring a brute force "just try every possible combination and see which is best" approach), build your own "combinatorial algorithm" in the spreadsheet that will compute the different combinations of row offset and height and subsequent RSQ() values, then locate the MAX() of all of the computed RSQ(). I put together this example that shows a couple of ways to generate permutations that should help show how this might be accomplished: https://www.excelforum.com/tips-and-...thout-vba.html
    4) Once you know which subset generates the MAX() RSQ(), then you can use the LOGEST()or LINEST() function to compute the regression parameters for that subset.

    That's a quick overview, what part(s) do you need further help with?
    Last edited by MrShorty; 06-13-2018 at 09:11 AM.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. Exponential Trendline formula
    By stopherlogic in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-01-2017, 08:34 AM
  2. Double exponential trendline
    By rbulph in forum Excel General
    Replies: 3
    Last Post: 09-16-2012, 10:38 AM
  3. Exponential trendline equation
    By xpat in forum Excel General
    Replies: 1
    Last Post: 07-12-2012, 12:10 AM
  4. [SOLVED] Is Exponential trendline always concave
    By ChemistB in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 05-31-2012, 01:47 PM
  5. Exponential trendline offset
    By drjulianof in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-22-2006, 06:10 AM
  6. Exponential trendline offset
    By drjulianof in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-20-2006, 04:55 PM
  7. Solver vs. Exponential Trendline
    By jcoleman52 in forum Excel General
    Replies: 2
    Last Post: 12-21-2005, 04:45 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