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.
