+ Reply to Thread
Results 1 to 9 of 9

2D Interpolation in Excel

  1. #1
    Registered User
    Join Date
    01-27-2016
    Location
    India
    MS-Off Ver
    2010
    Posts
    4

    2D Interpolation in Excel

    I have a table like this (2D Table)::
    ================================

    D1 L1 Values
    ==================
    0.1 - 1 - 0.050
    0.1 - 2 - 0.055
    0.1 - 3 - 0.090
    0.4 - 1 - 0.060
    0.4 - 2 - 0.072
    0.4 - 3 - 0.099
    0.7 - 1 - 1.0
    0.7 - 2 - 1.1
    0.7 - 3 - 2.0
    2.9 - 1 - 4.0
    2.9 - 2 - 5.2
    2.9 - 3 - 7.0


    Now I need to find a value at D1=1.5 & L1=2 point, can anyone give me a solution to do in excel?
    The table which is plotted is arbitrary but in actual case it might be monotonous in nature. But linear extrapolation will be good enough to find the desired value for D1 and L1 point.
    Last edited by anirbanphys; 01-27-2016 at 07:11 AM.

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

    Re: 2D Interpolation in Excel

    Can I assume you are looking for bilinear interpolation, or are you looking for a different multivariate interpolation algorithm? (https://en.wikipedia.org/wiki/Multiv..._interpolation ).

    Steps to perform bilinear interpolation:

    1) Use a lookup function (https://support.office.com/en-us/art...__toc309306714 ) to find the interval containing your desired x and y values.
    1a) I usually find that this is easier if the data are arranged in a table format rather than a list format:
    Please Login or Register  to view this content.
    I have found that a pivot table can be a quick and easy way to convert a list into a table like this. (http://www.excel-easy.com/data-analy...ot-tables.html see the section on 2D pivot tables).
    1b) Then use the MATCH() function (see function list help files above) to locate D1 and L1 in their respective columns rows.
    2) Once you have located the row and column that correspond to your entered D1 and L1, then you can use the INDEX() function to extract the four data points that represent the "corners" of the interpolation region
    3) With those four points extracted from the table, you can then apply the formula given by Wikipedia (click through to the article on bilinear interpolation) to find your desired z.

    Note that I would not try to do all of that in some single cell mega-formula. "Divide and conquer" by breaking it down into manageable steps like I describe.

    With it broken down like that, which part do you have trouble executing?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,278

    Re: 2D Interpolation in Excel

    I propose a solution to your problem with the dual-use functions percentile and additional row.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-27-2016
    Location
    India
    MS-Off Ver
    2010
    Posts
    4

    Re: 2D Interpolation in Excel

    Thanks MrShorty for your reply. Yes bilinear interpolation would be the best approach for this approximation. I understand table format is good rather than a list format but what I mentioned is just an example, in actual the data could be much much bigger (usually in my case it reaches 500-600). So my preference always will be list format. But as you suggested for the calculation table format is necessary so I will mange to get those data using some perl script.
    Can you please upload one excel file with your suggested approach? I am not able to get point no.2. Hope it will not be a big task for you.

    Thanks again.

  5. #5
    Registered User
    Join Date
    01-27-2016
    Location
    India
    MS-Off Ver
    2010
    Posts
    4

    Re: 2D Interpolation in Excel

    Thanks for your reply Czeslaw. Let me check your suggestion. Hopefully it will work. Can you suggest me is it possible to apply your approach on a list formatted sheet? If it is not advisable I will write a perl script or will follow pivot table suggested by MrShorty.
    Thanks for your help.

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

    Re: 2D Interpolation in Excel

    The key part of step 2 is the =INDEX() function, assuming you have arranged the data in a table as I suggested: https://support.office.com/en-us/art...2-b56b061328bd

    The index function is easy to use. You specify the desired range, then tell Excel which row and/or column you want returned.

    In this case, we have four (x,y,z) points to return. Our MATCH() functions (1b) have told us which row (i) the x value is in and which column (j) the y value is in. So we use the INDEX() function to return the four points
    x(i), y(j), z(i,j)
    x(i), y(j+1), z(i,j+1)
    x(i+1), y(j), z(i+1,j)
    x(i+1), y(j+1), z(i+1,j+1)

    Use of the INDEX() function for this is illustrated in attachment.

    On edit: Using one of the PERCENTILE() functions can work for 1D linear interpolation, however, I find that it is somewhat limited in its use. I recall researching this, but I don't recall the results. It was something like it is limited to continuously increasing/decreasing functions or monotonically increasing/decreasing functions or something like that. Because I often need linear interpolations that go beyond what the PERCENTILE functions can do, I prefer to stick with these more generic approaches to linear interpolation. When PERCENTILE() functions are applicable, they work very well for 1D interpolation. Since bilinear interpolation is basically a sequence of 1D interpolations, PERCENTILE() functions can work just fine for bilinear interpolation, if you get them strung together correctly.
    Attached Files Attached Files
    Last edited by MrShorty; 01-27-2016 at 05:53 PM.

  7. #7
    Registered User
    Join Date
    01-27-2016
    Location
    India
    MS-Off Ver
    2010
    Posts
    4

    Re: 2D Interpolation in Excel

    Thank you MrShorty, I got your point and successfully able to do the required things . And your view are highly accepted for my analysis.

  8. #8
    Registered User
    Join Date
    03-02-2016
    Location
    US
    MS-Off Ver
    Vista
    Posts
    5

    Re: 2D Interpolation in Excel

    I tried the solution proposed by Czeslaw using the percentile functions. However, I am running into a roadblock. In my situation, I have a certain value of D1 and one value in the cell. I will have to determine the value of L1.

    For eg., if my D1 is say, 0.3 and the value inside the pivot table is say, 0.059, I will need to find out the corresponding value of L1.

    Is there any way to determine it?

    Regards.

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

    Re: 2D Interpolation in Excel

    @miyaraj: This forum can be rather strict about not posting a new question in an old thread. It would be preferable to start your own thread. If this thread is important to understanding your question, feel free to include a link to this thread in your new thread.

+ 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. Interpolation in excel with VBA
    By john.smidth in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-26-2012, 01:01 AM
  2. Interpolation in Excel
    By Gio_it in forum Excel General
    Replies: 2
    Last Post: 02-23-2011, 12:01 PM
  3. Excel 2007 : Interpolation in excel
    By gannon_w in forum Excel General
    Replies: 3
    Last Post: 01-14-2010, 07:41 PM
  4. Replies: 0
    Last Post: 07-24-2008, 02:27 PM
  5. Interpolation in EXCEL
    By bg_nj in forum Excel General
    Replies: 1
    Last Post: 02-27-2008, 03:26 PM
  6. Need help with Interpolation in Excel
    By uwresearch in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-18-2007, 04:26 AM
  7. [SOLVED] help with interpolation and limit of interpolation
    By uriel78 in forum Excel General
    Replies: 0
    Last Post: 02-18-2005, 10:06 AM
  8. interpolation in excel
    By Elif in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-07-2005, 07: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