# Linear Interpolation Function - Using for non-linear data

#### pcarrollg

##### New Member
Hi,

I am trying to write a function that will interpolate between values in a table. I have two columns, x and y. I have a value for x, and I want to find the corresponding y based off of the data. I have a function I found that will perform a linear interpolation. However my data is not linear for the entire data set. But for small portions of the table (for example between rows 1 and 2, 2 and 3, 3 and 4... etc.) the data can be assumed to be linear.

What I want is to rewrite the linear interpolation function so that it can calculate based off of a small interval. Write now i am having trouble getting it to work, and keep getting a #Value error. I believe this is a result of the fact that my data is not sorted in terms of descending x values.

Here is the code I am using

Code:
``````Public Function Linterp(Tbl As Range, x As Double) As Variant
' linear interpolator / extrapolator
' Tbl is a two-column range containing known x, known y, sorted x descending

Dim nRow As Long
Dim iLo As Long, iHi As Long

nRow = Tbl.Rows.Count
If nRow < 2 Or Tbl.Columns.Count <> 2 Then
Linterp = CVErr(xlErrValue)
Exit Function '-------------------------------------------------------->
End If

If x > Tbl(1, 1) Then ' x > xmax, extrapolate from first two entries
iHi = 1
iLo = 2
ElseIf x < Tbl(nRow, 1) Then ' x < xmin, extrapolate from last two entries
iHi = nRow - 1
iLo = nRow
Else
iHi = Application.Match(x, Application.Index(Tbl, 0, 1), 1)
If Tbl(iHi, 1) = x Then ' x is exact from table
Linterp = Tbl(iHi, 2)
Exit Function '---------------------------------------------------->
Else ' x is between tabulated values, interpolate
iLo = iHi + 1
End If
End If

Linterp = Tbl(iLo, 2) + (Tbl(iHi, 2) - Tbl(iLo, 2)) _
* (x - Tbl(iLo, 1)) / (Tbl(iHi, 1) - Tbl(iLo, 1))

End Function``````

Here is an example set of data

 1.532 160 1.69 133 1.63 142 1.57 152 1.51 164 1.45 177 1.39 193 1.33 211 1.27 233 1.21 259 1.15 291 1.09 330 1.03 379 0.96 453 0.9 539 0.84 656 0.78 819 0.72 1058 0.66 1425 0.6 2027 0.54 3104

<colgroup><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Andrew Poulsom

##### MrExcel MVP
Welcome to MrExcel.

If your data is sorted ascending by column A you can use a formula like:

=FORECAST(C1,INDEX(B1:B21,MATCH(C1,A1:A21)):INDEX(B1:B21,MATCH(C1,A1:A21)+1),INDEX(A1:A21,MATCH(C1,A1:A21)):INDEX(A1:A21,MATCH(C1,A1:A21)+1))

#### shg

##### MrExcel MVP
If you sort the data by x, that routine will work (it's an older version of code I wrote some years ago).

In the alternative, since your data is strictly monotone, you could do it like this:

Code:
``````       --A-- -B-- C --D-- --E--
1     x    y       x     y
2   1.532  160   1.000 410.7
3   1.690  133
4   1.630  142
5   1.570  152
6   1.510  164
7   1.450  177
8   1.390  193
9   1.330  211
10   1.270  233
11   1.210  259
12   1.150  291
13   1.090  330
14   1.030  379
15   0.960  453
16   0.900  539
17   0.840  656
18   0.780  819
19   0.720 1058
20   0.660 1425
21   0.600 2027
22   0.540 3104``````
The formula in E2 is

=PERCENTILE(\$B\$2:\$B\$22, 1 - PERCENTRANK(\$A\$2:\$A\$22, D2, 6))

Last edited:

#### pcarrollg

##### New Member
Andrew,

That function works well for what I'm looking for. Could you just explain what it's actually doing? What is the purpose of the INDEX and MATCH functions used?

Also, I realized that there was an error in my data. The first value in the x column should be 1.75, so the x column is descending. I'm trying to write a Macro that will work for a large set of data. I need to interpolate this data between x and y. However in some cases, I will need to interpolate a y value based on a given x value, while in other cases I will need to interpolate an x value for a given y value. Do you think an if then type loop would be able to work for this?

Do you guys have any suggestions? I really appreciate the help so far.

Thanks

#### Andrew Poulsom

##### MrExcel MVP
INDEX is being used to return a reference - notice the colon separating the two calls to the function. The first reference is to the cell in the indexed column whose row in column A contains the largest value that is less than or equal to C1. The second reference is the cell below the first reference. The syntax for FORECAST is:

FORECAST(x, known_y's, known_x's)

so just adjust the column references for arguments 2 and 3 to suit the value you want to predict.

#### pcarrollg

##### New Member
I'm having some trouble getting the FORECAST function to work, and keep getting a #REF error. Going through the calculation steps, I believe this may be because I have my data sorted in descending x, and it is getting the error on the MATCH function.

I believe I need to change the match_type to -1. I'll try that and see if it works.

#### pcarrollg

##### New Member
Actually looking again at the Forecast Function you gave previously

=FORECAST(C1,INDEX(B1:B21,MATCH(C1,A1:A21)):INDEX(B1:B21,MATCH(C1,A1:A21)+1),INDEX(A1:A21,MATCH(C1,A1:A21)):INDEX(A1:A21 ,MATCH(C1,A1:A21)+1))

Will I have to change the +1 in the index column? So for example my code now looks like this.

=FORECAST(C1,INDEX(B1:B21,MATCH(C1,A1:A21,-1)):INDEX(B1:B21,MATCH(C1,A1:A21,-1)+1),INDEX(A1:A21,MATCH(C1,A1:A21,-1)):INDEX(A1:A21 ,MATCH(C1,A1:A21,-1)+1))

Will this work for descending x values?

Thanks again

#### Andrew Poulsom

##### MrExcel MVP
With a third argument of -1 MATCH finds the smallest value that is greater than or equal to lookup_value. So you still need the cell below (+1).

#### pcarrollg

##### New Member
Is there a way to recognize the last row of data that is used in my Range inside the Forecast function?

For the data I am interpolating, I need to be able to change the data used periodically. Is there a way I have have the R1C1 notation recognize the last row of data. For example right now the Forecast function above is using the range A1:A21 and B1:B21. However the number of rows may change to A1:A18 and B1:B18. Is there a way to have the code automatically recognize this?

#### Andrew Poulsom

##### MrExcel MVP
Is there anything below the last row containing data in your range? Incidentally, I don't think it matters if your data shrinks.

Replies
0
Views
352
Replies
4
Views
464
Replies
3
Views
2K
Replies
2
Views
3K
Replies
2
Views
2K

1,195,594
Messages
6,010,625
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back