Linear interpolation between nearest values -> must be able to extrapolate too...

L

LouisR

I have a function to interpolate between its two nearest values in
table. It must also be able to take the two largest and two smalles
value pairs, and extrapolate from there with their slope.

Any ideas, fixes, alternatives?

Thanks in advance,

Louis

Public Function FindX(xRange As Range, yRange As Range, _
y As Range, Optional bAscend As Boolean = True) As Double
Dim maxX As Double
Dim maxY As Double
Dim minX As Double
Dim minY As Double
Dim matchPoint As Long
Dim matchType As Long

If bAscend Then
matchType = 1
Else
matchType = -1
End If
matchPoint = Application.Match(y, yRange, matchType)
If yRange(matchPoint) = y Then
FindX = xRange(matchPoint)
Else
maxX = xRange(matchPoint - bAscend).Value
minX = xRange(matchPoint - (Not bAscend)).Value
maxY = yRange(matchPoint - bAscend).Value
minY = yRange(matchPoint - (Not bAscend)).Value
FindX = ((maxX - minX) / (maxY - minY) * (y - minY)) + minX
End If
End Functio
 
Top