+ Reply to Thread
Results 1 to 16 of 16

Bilinear interpolation of scattered data points

  1. #1
    Registered User
    Join Date
    09-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Bilinear interpolation of scattered data points

    Hello guys,
    I have a problem interpolating non equidistant points in Excel. Basically I have x,y,z Data for a lot of points in a grid.
    What I want to be able to do is to input not mapped x an y values and interpolate the corresponding z value.
    To make more clear what I mean I have attached a corresponding Excel sheet as an example. In reality what I have are about 100 x/y/z points in a close meshed grid.

    I don't even know if this is possible within Excel, but maybe some of you have an idea.


    Thanks
    Sebastian
    Attached Files Attached Files
    Last edited by Sebi; 09-15-2012 at 10:59 AM.

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

    Re: Bilinear interpolation of scattered data points

    The are a variety of methods described at http://en.wikipedia.org/wiki/Multiva...ttered_data.29. All, as a practical matter, would require VBA, and none would be trivial to implement.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    09-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Bilinear interpolation of scattered data points

    I know. The function I would need is the simplest of the methods. Just a bilinear Interpolation.
    But I know it's not trivial. I thought maybe someone here has written a function like this before and could give me some advice.

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

    Re: Bilinear interpolation of scattered data points

    Bilinear interpolation is intrinsically a calculation on a regular grid.

    A bi-cubic polynomial fit (using Solver) doesn't work very well:

    Please Login or Register  to view this content.
    You could try a harmonic fit (sines & cosines). What's the underlying data?

    There are surely add-ins that do this.
    Last edited by shg; 09-15-2012 at 12:43 PM.

  5. #5
    Registered User
    Join Date
    09-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Bilinear interpolation of scattered data points

    I am trying an to use add in using Kipling interpolation right now. The underlying data is an engine Map consisting of rpm and BMEP data on the x and y axis. The z axis is for example the fuel flow.
    This is what I am using.
    http://zible.free.fr/?page_id=1493

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

    Re: Bilinear interpolation of scattered data points

    Kipling? You mean Kriging?

  7. #7
    Registered User
    Join Date
    09-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Bilinear interpolation of scattered data points

    Yes sorry. I am doing ten things at the same time right now.. Kriging.

  8. #8
    Registered User
    Join Date
    09-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Bilinear interpolation of scattered data points

    Hey shy,
    how did you do the bi-cubic polynomial fit with solver?
    I would like to try that on my data as well but don't know how to do it.
    Thanks.

    And if someone already has code for an inverse distance weigthing interpolation method in VBA that would be great, too.
    I am trying to put a tool together but it will be more or less difficult.
    Last edited by Sebi; 09-17-2012 at 06:13 PM.

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

    Re: Bilinear interpolation of scattered data points

    I did. The formula in D8 and copied down is

    =MMULT(MMULT(CHOOSE({1,2,3,4}, 1, x, x^2, x^3), $A$1:$D$4), CHOOSE({1;2;3;4}, 1, y, y^2, y^3))

    ... where x refers to the value in col A and y to the value in col B.

    See attached.

    I think the Kriging approach would be MUCH better for this problem though.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Bilinear interpolation of scattered data points

    Yes,
    Kriging should be a lot better.
    But the disadvantage of Kriging is that I have to create a Variogram for each Map of Data Point for which I want to interpolate data.
    Because I need to do it for a lot of data this would take a lot of time.
    Therefore I have been looking into the Inverse Distance Weighting algorithm.
    The problem is that my programming skills are a little rusty so I am looking for an add in or VBA code that will do the IDW interpolation for me.

  11. #11
    Registered User
    Join Date
    09-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Bilinear interpolation of scattered data points

    shg I also don't really understand how you came up with the matrix at the top of your file for the bicubic interpolation.

    -A--- --B-- --C--- --D---
    1 1.024 1.011 0.959 -0.321
    2 0.820 0.645 -0.235 0.316
    3 0.313 0.279 -1.748 0.087
    4 -1.169 1.076 0.480 -0.058

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

    Re: Bilinear interpolation of scattered data points

    See the model in Solver.

  13. #13
    Registered User
    Join Date
    09-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Bilinear interpolation of scattered data points

    Now I have been trying to do the bicubic interpolation.
    But with my data the Error field shows a VALUE Error, which I can't figure out how to get to work.
    Last edited by Sebi; 09-19-2012 at 01:12 AM.

  14. #14
    Registered User
    Join Date
    09-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Bilinear interpolation of scattered data points

    Got it working but the error is way too high.

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

    Re: Bilinear interpolation of scattered data points

    Not surprising. As I said, some kind of local interpolation is surely better.

    Kriging seems more oriented to geospatial applications. For your application, perhaps interpolation based on contour lines is more appropriate. I'm interested in the solution, but really have nothing to suggest in terms of implementation.
    Last edited by shg; 09-19-2012 at 10:29 AM.

  16. #16
    Registered User
    Join Date
    09-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Bilinear interpolation of scattered data points

    At this point in my research Akima spline interpolation would be very useful and would fit my problem very well. Unfortunately I Don't think a VBA implementation will be easy so I might have to use MatLab or comparable software.

+ 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