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>