+ Reply to Thread
Results 1 to 5 of 5

Linear Interpolation

  1. #1
    Registered User
    Join Date
    08-19-2019
    Location
    USA
    MS-Off Ver
    10
    Posts
    4

    Linear Interpolation

    Hello everyone,

    I have two columns of data: "X" values in A1 thru A200 and their corresponding "Y" values in B1 thru B200.
    The numbers in A column starts from -1.4, 0.4 , 1.4 .... up to around 200 (none of those numbers are whole numbers)

    In C1 thru C200, I write whole numbers starting with 0 thru 200. These will be the new "X" values.
    In the D column I need the corresponding, linear interpolated "Y" values" for each whole number "X" values.

    How can I write interpolation in the D Column? (the intervals between each "X" values are not always uniformly spaced)

    Thank you

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Linear Interpolation

    Have you tried linest() function to derive a line of best fit?
    Happy with my advice? Click on the * reputation button below

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

    Re: Linear Interpolation

    Are you required to use Excel? This is perfectly straightforward using Gnumeric's INTERPOLATION() function D1 is simply =INTERPOLATION($B$1:$B$200,$A$1:$A$200,C1) copied down.

    If you must use Excel, I usually recommend something like this: https://www.excelforum.com/excel-for...ml#post5262186 where you use two additional helper columns to compute slope and intercept for each segment, then use a lookup function (like VLOOKUP()) to extract the slope and intercept at your new x value.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    08-19-2019
    Location
    USA
    MS-Off Ver
    10
    Posts
    4

    Re: Linear Interpolation

    I could not quite get it to work. Maybe I didn't write question correctly.
    I saw that we could upload a sample worksheet.
    Could you take a look the sample sheet.
    Basically I need the E-column to give me interpolated "reading" based on the whole number depth in D-column.
    And the intervals of the depths in column A is not uniform.

    Thank you
    Attached Files Attached Files

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

    Re: Linear Interpolation

    I did not see any attempt at entering SLOPE() or INTERCEPT() or VLOOKUP() or anything, so I'm not sure what did not work for you. What I expected was something like:

    0) Insert 3 columns between the input table and the output table to make room for the slope and intercept columns (so the output depth column is now column G).
    1) In C2, enter =SLOPE(B2:B3,A2:A3). Copy/paste/fill down to C19.
    2) In D2, enter =INTERCEPT(B2:B3,A2:A3). Copy/paste/fill down to D19.
    3) In H2 (the interpolated reading column) enter =VLOOKUP(G2,$A$2:$D$19,4,TRUE)+VLOOKUP(G2,$A$2:$D$19,3,TRUE)*G2. Copy/paste/fill down to H22.

    Which of those steps are you having trouble with?

+ 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. Seeking tutoring HELP! with Linear Interpolation formula inputs
    By kashbay in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-09-2017, 10:55 PM
  2. Interpolation, non linear. Need help please.
    By traumerei1838 in forum Excel General
    Replies: 8
    Last Post: 08-12-2015, 01:04 AM
  3. [SOLVED] how to do linear interpolation
    By fight2 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-11-2015, 08:40 PM
  4. Linear Interpolation help
    By jsuarez199 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-21-2013, 03:12 PM
  5. VBA Linear Interpolation
    By WAW in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-25-2010, 04:55 PM
  6. Linear interpolation
    By bruteforce in forum Excel General
    Replies: 0
    Last Post: 04-25-2009, 01:11 PM
  7. [SOLVED] linear interpolation
    By Taha in forum Excel General
    Replies: 3
    Last Post: 01-31-2005, 11:06 AM

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