# Bilinear Interpolation VBA function for range

1. ## 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..

2. ## 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:
`Please Login or Register  to view this content.`
copied down and right
then in P14 (and copied right) your "step 2":
Formula:
`Please Login or Register  to view this content.`

and finally in Q16 array* formula:
Formula:
`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.

3. ## 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

Originally Posted by Kaper
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:
`Please Login or Register  to view this content.`
copied down and right
then in P14 (and copied right) your "step 2":
Formula:
`Please Login or Register  to view this content.`

and finally in Q16 array* formula:
Formula:
`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. ## 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:
`Please Login or Register  to view this content.`

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.``

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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