How to Perform Bilinear Interpolation in Excel (with Easy Steps)

Bilinear interpolation is a mathematical process to estimate the function value of two variables when you do not know the function values of the input variables. Bilinear interpolation in Microsoft Excel is simpler than doing it in Matlab.

What Is Bilinear Interpolation?

When you want to find the value of a function f(x,y) having two variables, bilinear interpolation is a method of estimating value even when the proper function is unknown. Suppose you have only two variables and their output, but you do not know the equation of the function. Bilinear interpolation helps you estimate the output of two input variables.

The equation for bilinear interpolation is given below:

f(x,y) = (1/(x_2-x_1)(y_2-y_1))*(q_11(x_2-x)(y_2-y)+q_21(x-x_1)(y_2-y)+q_12(x_2-x)(y-y_1)+q_22(x-x_1)(y-y_1))

Here,

x = input value of x

y = input variable y

x_1 = very close adjacent below value of x from given data

x_2 = very close adjacent above value of x from given data

y_1 = very close adjacent below value of y from given data

y_2 = very close adjacent above value of y from given data

q_11 = function value of x_1, y_1

q_12 = function value of x_1, y_2

q_21 = function value of x_2, y_1

q_22 = function value of x_2, y_2

Perform Bilinear Interpolation in Excel: Steps-by-Steps Procedures

This article gives the easiest step-by-step procedures for bilinear interpolation. Here, you just find the above and below values of adjacent input and find the function value. To accomplish the task, we will extract those numbers, use them in the formula, and then find the output.

Step 1: Making Dataset with Variables

Here is a dataset where function values for different variables of two are given. You can give any arbitrary value or collect data from an inspection at work, but the input data must be in increasing order.

Step 2: Providing Input Values and Their Names

Here, we will give an input value and name the input value to apply the formula easily.

Firstly, we have given input 17.
Then, we will assign the name to the value as x for applying the formula easily.

Now, we will provide the input value of y and name the value as y as you have taken x.

Step 3: Selecting Cell and Ranges Adjacent to Input Values and Names

Here, input x = 17 and y = 45, so the adjacent value must be x = 16 to x = 18 and y = 40 to y = 50.

So, we have selected x = 16 to x = 18 because they are adjacent to x = 17 below and above respectively.
Then, name them as xvalue.

Then, likewise, y = 40 to y = 50 as they are adjacent to input y=45 below and above respectively.
Then, we will name them as yvalues. You can give anything you want and use the name in the formula accordingly.

Now, select the corresponding function values and name them zvalues.

Step 4: Finding Look Up Values

Here we are going to find look up values of x1, x2, y1, y2, q11, q12, q21, q22 and name them as x_1, x_2, y_1, y_2, q_11, q_12, q_21, q_22.

Here, we have used INDEX and MATCH functions to find the below adjacent value.
Now, the MATCH function finds the below adjacent value of input value because the MATCH function argument has 1 and the INDEX function returns the value.
Now, the MATCH function finds the adjacent below of input x, and the INDEX function returns the value.

=INDEX(xvalue,MATCH(x,xvalue,1))

To find x2, we have to take the above value adjacent to the input value of x. That’s why we will increase the argument of the MATCH function by 1 to indicate the next index.

=INDEX(xvalue,MATCH(x,xvalue,1)+1)

Afterward, we will find the value of y1 and name it y_1. This is the exact same process as x1.

=INDEX(yvalues,MATCH(y,yvalues,1))

Similarly, we will find the value of y2 and name it y_2. This is the exact same process as x2.

=INDEX(yvalues,MATCH(y,yvalues,1)+1)

Now, q11 is the value of the function where x = x_1 and y = y_1.
The MATCH function gives the relative position of x_1 and y_1, and the INDEX function extracts the function value.

=INDEX(zvalues,MATCH(y_1,yvalues,0),MATCH(x_1,xvalue,0))

Like above you can find q_12.

=INDEX(zvalues,MATCH(y_2,yvalues,0),MATCH(x_1,xvalue,0))

Just like before, find the value of q21 and name it q_21.

=INDEX(zvalues,MATCH(y_1,yvalues,0),MATCH(x_2,xvalue,0))

Finally, find q22 and name it q_22.

=INDEX(zvalues,MATCH(y_2,yvalues,0),MATCH(x_2,xvalue,0))

Step 5: Showing Final Results

Then, using the lookup values and input x and y, we will find the interpolated value of the function.

Therefore, the bilinear interpolation result is shown in cell I15 as 6.

=1/((x_2-x_1)*(y_2-y_1))*(q_11*(x_2-x)*(y_2-y)+q_21*(x-x_1)*(y_2-y)+q_12*(x_2-x)*(y-y_1)+q_22*(x-x_1)*(y-y_1))

Download Practice Workbook

You may download the following workbook to practice yourself.

Making Bilinear Interpolation.xlsx

Conclusion

In this article, we have provided the easiest way to perform Bilinear Interpolation in Excel. If you only follow the image, you can perform interpolation in your data to estimate the value that you want.

<< Go Back to Excel Interpolation | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!