# How to perform Linear Interpolation Excel VBA – 4 Steps

This is a final output:

## What Is Linear Interpolation in Excel?

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

Suppose you have a point W(a,b) and V(c,d). For an unknown point of U(x,y), you want to know the y value for the known value of x. TThe equation is:

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

### Step 1- Create a Dataset

• The dataset showcases Solubility in Different Temperatures.

To know the solubility of a fixed temperature:

### Step 2 – Open the Visual Basic Editor

• In the Visual Basic Editor, choose Insert  >> Module >> Module1.

Note: You can also open the Visual Basic Editor pressing ALT + F11.

### Step 3 – Enter VBA code

• In Module 1, use 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 Breakdown

The Lin function is declared.  WorksheetFunction Index and Match is called. The VBA Match function finds the relative position of the largest value of w and v which is the range of data in your dataset. The VBA Index function will return the largest value obtained by the Match function. The same formula is used for the y value of w, but  +1 is inserted in the value after the largest value. c and d, which are the coordinates of point v are set.

### Step 4- Use the Function

• Run the code by pressing F5.
• Go to C13 and enter the formula.
`=Lin(C12,B5:B10,C5:C10)`

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 perform linear interpolation.

• Press ENTER.

This is the output.

If you change the known value of Temperature, the Solubility changes. See the image below.

Practice here.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF