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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview Image


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

 Formula For Bilinear Interpolation in Excel

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.

Making Data Set with Variables


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.

Providing Input Values and Their Names for Bilinear Interpolation in Excel

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

Providing Input Values and Their Names

Read More: How to Do Linear Interpolation in Excel


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.

Selecting Cell and Ranges Adjacent to Input Values and Names for Bilinear Interpolation in Excel

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

Selecting Cell and Ranges Adjacent to Input Values and Names

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

Selecting Cell and Ranges Adjacent to Input Values and Names for Bilinear Interpolation in Excel


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

Finding Look Up Values for Bilinear Interpolation in Excel

  • 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)

Finding Look Up Values

  • 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))

Finding Look Up Values for Bilinear Interpolation in Excel

  • 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)

Finding Look Up Values

  • 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))

Finding Look Up Values for Bilinear Interpolation in Excel

  • Like above you can find q_12.
=INDEX(zvalues,MATCH(y_2,yvalues,0),MATCH(x_1,xvalue,0))

Finding Look Up Values for Bilinear Interpolation in Excel

  • 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))

Finding Look Up Values for Bilinear Interpolation in Excel

  • Finally, find q22 and name it q_22.
=INDEX(zvalues,MATCH(y_2,yvalues,0),MATCH(x_2,xvalue,0))

Finding Look Up Values for Bilinear Interpolation in Excel


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

Showing Result of Bilinear Interpolation

Read More: How to Use Non Linear Interpolation in Excel


Download Practice Workbook

You may download the following workbook to practice yourself.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Joyanta Mitra
Joyanta Mitra

Joyanta Mitra, a BSc graduate in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. Specializing in programming, he has authored and modified 60 articles, predominantly focusing on Power Query and VBA (Visual Basic for Applications). His expertise in VBA programming is evident through the substantial body of work he has contributed, showcasing a deep understanding of Excel automation, and enhancing the ExcelDemy project's resources with valuable... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo