Hi
I have written a UDF that does simple linear interpolation. The code is pasted below.
Am I reinventing the wheel ? Does excel have a function that does this ?
Cheers
Orekin
I have written a UDF that does simple linear interpolation. The code is pasted below.
Am I reinventing the wheel ? Does excel have a function that does this ?
Cheers
Orekin
Code:
Public Function XL_LinearInterpolate(xValue As Double, inputRange As Range) As Double
Dim xyValues() As Double
Call CopyRangeToDoubleArray(inputRange, xyValues())
XL_LinearInterpolate = LinearInterpolate(xValue, xyValues())
End Function
Public Function LinearInterpolate(xValue As Double, xyValues() As Double) As Double
'Assumes that:
'dataset() is sorted ascending by first column
'x vals in first column, y values in second column
Dim i As Long
'If outside range, then flat line it
If (xValue <= xyValues()(1, 1)) Then
LinearInterpolate = xyValues()(1, 2)
Exit Function
ElseIf (xValue >= xyValues()(UBound(xyValues), 1)) Then
LinearInterpolate = xyValues()(UBound(xyValues), 2)
Exit Function
End If
'Check within the range
For i = 1 To UBound(xyValues()) - 1 Step 1
If (xValue >= xyValues()(i, 1) And xValue <= xyValues()(i + 1, 1)) Then
LinearInterpolate = xyValues(i, 2) + (xyValues(i + 1, 2) - xyValues(i, 2)) * (xValue - xyValues(i, 1)) / ((xyValues(i + 1, 1) - xyValues(i, 1)))
Exit Function
End If
Next i
End Function