# Linear Interpolation Excel Formula

Linear interpolation is the simplest way to get the unknown data based on the known data set or coordinates. Linear interpolation is widely used in the areas of finance and mathematics to carry out forecasting, investment, or depreciation cost calculations. In this article, we will learn how to do linear interpolation in Excel

## The theory you should know about liner interpolation:

## The formula for Liner Interpolation is:

`y = y1 + (x - x1) ⨯ (y2 - y1) / (x2 - x1)`

where** (x1, y1)** and **(x2, y2)** are known values or coordinates. By inserting the value of **x **or **y**, you can obtain the value of the other.

Here, we will discuss three methods to do Linear Interpolation in Excel

- Linear Interpolation in Excel using Formula
- Linear interpolation by Function in Excel
- Linear interpolation in Excel by Trendline

## Method 01: Linear Interpolation in Excel using Formula

Problem source: Applied Numerical Methods with MATLAB for Engineers, Chapter 17 (Page 410, Example 17.2)

Let’s solve this problem in Excel

Here given values are** ln 1 **= 0 and** ln 6** = 1.791759

Vale needs to know, **ln 2** =?

Use the following formula in cell **D7**

`=D4+((D5-D4)/(C5-C4))*(C7-C4)`

Similarly, repeat the same calculation when the known values are **ln 1** = 0 and** ln 4** = 1.386294

`=D12+((D13-D12)/(C13-C12))*(C15-C12)`

Given that, the Exact value** In 2** = 0.6931471

And we get the values of** ln 2** as 0.358352 and 0.462098.

So, we can say that if we do linear interpolation, taking known points close to the unknown points, the results will be more accurate.

## Method 02: Linear interpolation by Function in Excel

Excel provides a built-in formula named **FORECAST** to perform linear interpolation

### Syntax of FORECAST formula

`=FORECAST(x, known_y, known_x)`

### Argument of FORECAST formula

**x:** the value for which you want to predict the corresponding y-value

**known_y:** an array or range of y-values that correspond to the known_x

**known_x:** an array or range of x-values that correspond to the known_y

**Example:** Suppose you are given the following data table of days and corresponding sell quantities. And you want to know the sell quantity for the next 30 days.

Apply the following formula to get the sell quantities for the next 30 days.

`=FORECAST(G4,D3:D12,C3:C12)`

## Method 03: Linear interpolation in Excel by Trendline

You can also perform linear interpolation in Excel by importing the trendline for the given value of** x** and** y**

Suppose you want to know the value of y for** x=20 **based on the following given data set

**Step 01**: Select the range **B2:C12** of the given value of x and y

**Step 02**: Navigate to the** Insert** tab

**Step 03**: From the **chart** section, click on the **scatter chart.**

The result is

**Step 04**: Double-click on the chart. The **Chart Design** tab will open

**Step 05**: Click on the **Add Chart Element**

**Step 06**: Keep the mouse pointer on the **Trendline** option. Then, Click on the **Liner **option

The result is

**Step 07**: Double-click on the trendline. The **Format Trendline** tab Will arrive on the right side of the monitor

**Step 08**: Click on the **Trendline options**. Then, Scroll down and Check in the **Display Equation on the Chat** option

**Step 09**: Write the trendline equation in Excel as given below. Then insert it into cell **C17**

`=4.2788*C16-2.3333 //returns 83.2427`

Where cell **C16 **contains the value of **x**