+ Reply to Thread
Results 1 to 4 of 4

Bilinear Interpolation VBA function for range

  1. #1
    Registered User
    Join Date
    02-28-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    12

    Bilinear Interpolation VBA function for range

    I am using Bilinear function to interpolate within a table.

    Now the argument for Bilinear function is =Bilinterp(X,Y,"Range")

    Step 1: X values comes from another table, and so is Y

    Step 2: Sum over (Bilinterp(x1, a1*y1, range)+ Bilinterp(x1, a2*y2, range) and so on..
    Lets the value of above is value 1. Similarly Value 2 for X2

    Step 3: Sum b1* value 1 + b2* value 2 and so on..

    Please help me. I am attaching the file
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Bilinear Interpolation VBA function for range

    Well, data is a bit inconsistant - you look for X 6.5 while no data for 6. Also for Y and X = 15.5, while no input data for 16.

    you can change formulas as you complete missing data.

    at the moment - I see no need to create custom functions in VBA - you can calculate this easily in the worksheet - see attached, there are only 3 formulas:

    in P6 (I prepared intermediate stage table headers with X 6.5, 7.5 etc in column O and row 5) - your step1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copied down and right
    then in P14 (and copied right) your "step 2":
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and finally in Q16 array* formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    not in P16 because you gave no coefficient for X=6.5 (coeff. table starts from 7.0-8.0)


    *...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    02-28-2015
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    12

    Re: Bilinear Interpolation VBA function for range

    Thank you so much.. firstly sorry for inconsistent data.


    May i ask if this can be done without the external table being created, as I am looking for final number. If this is possible to include into one formula, or if I can create my own formula that takes in

    (X range value, X coefficient range values, Y range Values, Y Coefficient range value, Table of X and Y)

    and gives the output.

    Thank you for the kind attention

    Regards
    Rajsh




    Quote Originally Posted by Kaper View Post
    Well, data is a bit inconsistant - you look for X 6.5 while no data for 6. Also for Y and X = 15.5, while no input data for 16.

    you can change formulas as you complete missing data.

    at the moment - I see no need to create custom functions in VBA - you can calculate this easily in the worksheet - see attached, there are only 3 formulas:

    in P6 (I prepared intermediate stage table headers with X 6.5, 7.5 etc in column O and row 5) - your step1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copied down and right
    then in P14 (and copied right) your "step 2":
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and finally in Q16 array* formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    not in P16 because you gave no coefficient for X=6.5 (coeff. table starts from 7.0-8.0)


    *...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Bilinear Interpolation VBA function for range

    Well,
    I did a kind of "wraper" to this function (which was already a wraper for another one)

    After correcting data try
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    in your sheet

    see attachment (all data in columns N and to the right is left only to verify the result. You can delete it.).

    The code for the function is:
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. Need help: VLookup/Index: Tricky Bilinear interpolation
    By rajsh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-04-2015, 10:54 AM
  2. [SOLVED] Bilinear Interpolation- help needed
    By rajsh in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-28-2015, 05:29 AM
  3. Bilinear interpolation of scattered data points
    By Sebi in forum Excel General
    Replies: 15
    Last Post: 09-19-2012, 01:05 AM
  4. Bilinear interpolation of irregular grid
    By Sebi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-15-2012, 05:19 PM
  5. Replies: 0
    Last Post: 07-24-2008, 02:27 PM

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