Sometimes, you may deal with a dataset that follows a linear regression. You want to know an unknown value from a known value of that dataset. In this case, you need to do a linear interpolation. It helps you to find out the unknown data point. There are several functions to perform the interpolation. But whenever you need to create a custom function, you need the help of VBA Macros. It can create a function that will do a linear interpolation. In this article, we’re going to show you some easy steps to do linear interpolation with VBA in Excel. So, let’s get started.

Hereâ€™s the final output that you will get by the end of the article.

## What Is Linear Interpolation in Excel?

Interpolation is a method of finding a value from known values. In linear interpolation, you might get a new data point from two known data points.

Suppose you have a point W(a,b) and V(c,d). Now, for an unknown point of U(x,y), you want to know the** y** value for the known value of **x**. There is a very common mathematical equation for this scenario. The equation is:

`y=c + ((d - c) * (x - a) / (b - a))`

From the above equation, you can find the unknown **y **value.

## Calculate Linear Interpolation with VBA in Excel: 4 Easy Steps

There are several functions from which you can do linear interpolation in Excel. Also, you can create a function with the help of VBA from where you will be able to find the unknown value.

Here, we have used the ** Microsoft 365** version. You may use any other version at your convenience.

### Step 01: Create a Dataset

- In the beginning, you need to create a dataset. In our case, we have taken a dataset of
*Solubility in Different Temperatures*. We all know that solubility changes with respect to temperature.

Now, we want to know the solubility of a fixed temperature, which is not provided in the dataset.

### Step 02: Open Visual Basic Editor

- We need to create a function that will do our job for this interpolation. For creating a User Defined Function in Excel, firstly, navigate to the
**Developer**tab and move to**Visual Basic**.

- Apparently,
**Visual Basic Editor**opens. Choose**Insert**tab >>**Module**>>**Module1**from there.

*Note**: you can also open the Visual Basic Editor with ALT + F11 key.*

### Step 03: Insert Function with VBA Macros

- In
**Module 1**, write up the following code.

```
Function Lin(x, w, v)
a = Application.WorksheetFunction.Index(w, Application.WorksheetFunction.Match(x, w, 1))
b = Application.WorksheetFunction.Index(w, Application.WorksheetFunction.Match(x, w, 1) + 1)
c = Application.WorksheetFunction.Index(v, Application.WorksheetFunction.Match(x, w, 1))
d = Application.WorksheetFunction.Index(v, Application.WorksheetFunction.Match(x, w, 1) + 1)
Lin = c + ((d - c) * (x - a) / (b - a))
End Function
```

**Code Explanation:**

We have declared a function named **Lin** here. We called out the **WorksheetFunction** **Index and Match**. The **VBA Match** function finds the relative position of the largest value of **w** and **v **which is the range of data you have in your dataset. The **VBA Index** function will return the largest value obtained by the **Match** function. Then, we used the same formula for the **y** value of the point **w**, but here, we insert **+1** for the value after the largest value. Similarly, we set **c **and **d**, which are the coordinates of point **v**. We wrote up the interpolation equation and named it the **Lin** function.

### Step 04: Use the Function for Interpolation

- Eventually, run the code with the
**F5**key and it will create the function. - Sequentially, move to cell
**C13**and insert the below formula.

`=Lin(C12,B5:B10,C5:C10)`

Here, **C12** is the known **x **value. **B5:B10** is the range of the **w** value, and **C5:C10** is the range of the **v** value.

The custom **Lin** function will do linear interpolation from the given data.

- Eventually, press
**ENTER**.

You will get the following output, like the image below.

Moreover, if you change the known value of the *Temperature*, the *Solubility* changes accordingly. See the below image for a better understanding.

**Read More:** How to Use Non Linear Interpolation in Excel

## Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

**Download Practice Workbook**

Download the following practice workbook. It will help you to realize the topic more clearly.

## Conclusion

Thatâ€™s all about todayâ€™s session. These are some easy steps for linear interpolation with VBA in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet.

## 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 Perform Bilinear Interpolation in Excel
- How to Interpolate in Excel Graph

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