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.

**Table of Contents**Expand

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

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

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

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

- How to Interpolate Missing Data in Excel
- How to Do VLOOKUP and Interpolate in Excel
- How to Do Interpolation with GROWTH & TREND Functions in Excel
- How to Interpolate Between Two Values in Excel
- How to Interpolate in Excel Graph
- How to Do Linear Interpolation Excel VBA

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