+ Reply to Thread
Results 1 to 8 of 8

Extrapolating data within a table

  1. #1
    Registered User
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    Extrapolating data within a table

    Good morning,

    I've been tasked with fixing up a spreadsheet to cut down a lot of time. I've found a UDF that can interpolate my data easily ( http://www.dailydoseofexcel.com/arch...ookup-between/ ).

    My table has several columns and all but one are filled out to the extent of the X data, where as one column is cut off at about half way through. I'd like to extrapolate the incomplete data based on the last two points of information to the range of the X data and then I can use the interpolate function to grab any data inbetween. I know I can do this using the trend() function but is there a way to do it with a macro so anyone else using the spreadsheet can plug in their table of information (which may be have more or less lines of data)?

    I've attached an example table -- I'm looking to extrapolate the C values to the range of the X values based on the last two available C points of data. Is this possible/if so is there an existing macro that I could use?

    Thanks in advance for your help and I apologise if this is considered a really stupid question... but I learned a tiny bit of VB years ago and am simply not very familar with all of the Excel VB functions.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Extrapolating data within a table

    Just wondering if anyone knows if this is possible?

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Extrapolating data within a table

    To extrapolate linearly from the last two points, in D10 and copy down,

    =$D$9 + (A10-$A$9) * ($D$9-$D$8) / ($A$9-$A$8)
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Extrapolating data within a table

    Quote Originally Posted by shg View Post
    To extrapolate linearly from the last two points, in D10 and copy down,

    =$D$9 + (A10-$A$9) * ($D$9-$D$8) / ($A$9-$A$8)
    shg,

    Thanks for your suggestion. I'm aware of how to extrapolate based on this case but the inputted data in that table may have more or less points of data so your suggested formula won't work unless there are exactly this many points of data.

    Is it possible to extrapolate not knowing how many points of data there will be?

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Extrapolating data within a table

    I don't know enough about what you're trying to do; I answered the question for the data you provided. Can you not change the references if the data ends in a different row?

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

    Re: Extrapolating data within a table

    Would it work if you took out the absolute references ($)? =$D9 + (A10-$A9) * ($D9-$D8) / ($A9-$A8). Where all of the extrapolated points are collinear with the last two entered values, linear extrapolation based on any two of the points (including the previous two in the series), should yield the same result. Then when someone copies in a larger table than original, the formula won't need to be adjusted. The tricky situation is how to deal with the case when someone copies in a smaller table than given. I don't know exactly how I would handle that in your situation -- maybe build a "template spreadsheet" with the smallest possible entered table (two rows). Then train users to always start with that template when entering their own data.

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Extrapolating data within a table

    Would it work if you took out the absolute references ($)? =$D9 + (A10-$A9) * ($D9-$D8) / ($A9-$A8). Where all of the extrapolated points are collinear with the last two entered values, linear extrapolation based on any two of the points (including the previous two in the series), should yield the same result.
    Yes indeed.

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Extrapolating data within a table

    You could instead use in D10,

    =TREND(D8:D9, A8:A9, A10)

    Note that just references to last two y values, the last two x values, and the new x.

+ 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