# Does Excel Have an Interpolation Function ?

#### orekin

##### Board Regular
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

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``````

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Replies
3
Views
71
Replies
19
Views
183
Replies
1
Views
125
Replies
0
Views
130
Replies
2
Views
142