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
Here is an example set of data
<colgroup><col><col></colgroup><tbody>
</tbody>
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.690 | 133 |
1.630 | 142 |
1.570 | 152 |
1.510 | 164 |
1.450 | 177 |
1.390 | 193 |
1.330 | 211 |
1.270 | 233 |
1.210 | 259 |
1.150 | 291 |
1.090 | 330 |
1.030 | 379 |
0.960 | 453 |
0.900 | 539 |
0.840 | 656 |
0.780 | 819 |
0.720 | 1058 |
0.660 | 1425 |
0.600 | 2027 |
0.540 | 3104 |
<colgroup><col><col></colgroup><tbody>
</tbody>