You are given the **X, Y** Coordinates of 2 points, (99.5,39.5) and (201.1,45.7) and asked to find the **Y** coordinate of an intermediate point with an **X** coordinate of **115.7**. How will you solve this problem?

The answer is **Interpolation!**

Interpolation is the method of estimating ‘unknown values’ that fall between ‘known values’.

In this blog post, I will explain 2 Excel formulas to perform Linear interpolation to find the unknown.

Table of Contents

### Linear Interpolation Formula

If there are 2 known points denoted by the coordinates (**x _{1}**,

**y**) and (

_{1}**x**

_{2}**y**), the y coordinate of

_{2}**x**in between

**x**and

_{1}**x**can be found out by the following formula

_{2}See the following problem. If **x _{1}** = 99.5,

**y**= 39.5,

_{1}**x**= 201.1 and

_{2}**y**= 45.7, what is the y coordinate of the point with x coordinate,115.7?

_{2}When the above values are supplied into the Linear Interpolation formula, the formula to find the **y** coordinate will become,

=(39.5*(201.1-115.7)+45.7*(115.7-99.5))/(201.1-99.5)

In our Excel worksheet, **x _{1}** is in B3,

**y**in C3,

_{1}**x**in B6,

_{2}**y**in C6 and

_{2}**x**is in E3. So, the Excel formula equivalent to the above formula is,

=(C3*(B6-E3)+C6*(E3-B3))/(B6-B3)

and the formula evaluates to 40.49.

### Interpolation with FORECAST.LINEAR function

FORECAST.LINEAR function in Excel is used to predict a linear trend based on known values and the same can be used to perform Interpolation.

Let’s solve the same problem that we discussed above, using FORECAST.LINEAR function.

**x _{1}** = 99.5 (B3),

**y**= 39.5 (C3),

_{1}**x**= 201.1 (B4),

_{2}**y**= 45.7 (C4) and we need to find the

_{2}**y**when

**x**= 115.7 (E3)

FORECAST.LINEAR has 3 arguments* x, known_ys, and known_xs*.

Here,* x*** **is 115.7, the value in the cell E3.

*known_ys*** **are 39.5 and 45.7, the values in the cells C3 and C4.

*known_xs*** **are 99.5 and 201.1, the values in the cells B3 and B4.

So, the formula to calculate y value is,

=FORECAST.LINEAR(E3,C3:C4,B3:B4)

and the formula will return 40.49.

### Calculating Y values against X values at regular intervals

Following is another problem where we need to find the **Y** values against **X** values at 10-meter intervals (Column E).

#### Method 1

Solution using Linear Interpolation Formula.

We have **x _{1}** in B4,

**y**in C4,

_{1}**x**in B5,

_{2}**y**in C5 and

_{2}**x**in E4

Linear Interpolation formula to find the **Y** coordinate against the **X** coordinates value in E4 (100) is,

=(((C5-C4)/(B5-B4))*(E4-B4))+C4

After locking the required cell references, the same formula can be copied into the adjacent cells below to calculate the **Y** coordinates for the remaining **X** values.

=((($C$5-$C$4)/($B$5-$B$4))*(E4-$B$4))+$C$4

#### Method 2

The same problem solved using FORECAST.LINEAR function.

We have **x _{1}** is in B4,

**y**in C4,

_{1}**x**in B5,

_{2}**y**in C5 and

_{2}**x**in E4

Formula to find the **Y** coordinate against the **X** coordinates value in E4 (100) is,

=FORECAST.LINEAR(E4,C4:C5,B4:B5)

After locking the required cell references, the same formula can be copied into the adjacent cells below to calculate the **Y** coordinates for the remaining **X** values.

=FORECAST.LINEAR(E4,$C$4:$C$5,$B$4:$B$5)

Excel Functions in Alphabetical Order (Complete list)

Complete List of Excel Functions (Category wise)