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

Bilinear interpolation is a technique used to estimate function values for two variables when the actual function is unknown. In Microsoft Excel, you can perform bilinear interpolation more easily than 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 the 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

### Step 1 – Creating a Dataset with Variables

Create a dataset with function values for different variable pairs. Ensure the input data is in increasing order.

### Step 2 – Providing Input Values and Their Names

• Given an input value of 17, name it x.

• Provide an input value for y (you’ve taken x), and name it accordingly.

Read More: How to Do Linear Interpolation in Excel

### Step 3 – Selecting Cells Adjacent to Input Values and Names

• For input x = 17 and y = 45, select adjacent values: x = 16 to x = 18 and y = 40 to y = 50.
• Name these ranges as xvalue and yvalues.

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

### Step 4 – Finding Look Up Values

• Use INDEX and MATCH functions to find:
• x_1 (just below desired x-value of 235): =INDEX(xvalue, MATCH(x, xvalue, 1)).

• x_2 (just above desired x-value): =INDEX(xvalue, MATCH(x, xvalue, 1) + 1).

• y_1 (similar process as x_1): =INDEX(yvalues, MATCH(y, yvalues, 1)).

• y_2 (similar process as x_2): =INDEX(yvalues, MATCH(y, yvalues, 1) + 1).

• q_11 (function value at x_1, y_1): =INDEX(zvalues, MATCH(y_1, yvalues, 0), MATCH(x_1, xvalue, 0)).

• q_12 (similar process as q_11): =INDEX(zvalues, MATCH(y_2, yvalues, 0), MATCH(x_1, xvalue, 0)).

• q_21 (similar process as q_11): =INDEX(zvalues, MATCH(y_1, yvalues, 0), MATCH(x_2, xvalue, 0)).

• q_22 (similar process as q_11): =INDEX(zvalues, MATCH(y_2, yvalues, 0), MATCH(x_2, xvalue, 0)).

### Step 5 – Showing Final Results

We calculate the final result using the lookup values and input x and y. The bilinear interpolation formula is as follows:

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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF