Linear Interpolation

[Trine.engr]

I'm currently in college as a Civil Engineer. I do a lot of spreadsheets that requires linear interpolation using arrays. I've been trying to work on a workbook function that will see the number that I'm looking at, find that number between two others, and then report back to me the vlookup value associated with that interpolation. I have attached my workbook (note: this is only an example of what I would like to be done and not a hmwk problem). For example I would like to input =interpolation(2.5,B4: D14,3)------- (=interpolation(number_used_to_interpolate, array_to_look_from, col_index_answer) to give me an answer of 0.875.

Thanks for your help

[mikerickson]

Something very similar can be done with native excel (formulas and/or Named functions).
http://www.mrexcel.com/forum/showthread.php?t=365486&highlight=interpolation

(Note that in the link, the helper columns are for explanation only)

But it sounds like you need a UDF.

[mikerickson]

You might use this.
The Sorted argument should be True (the default) if the range with the x values is sorted either ascending or descending. An unordered x Range should have Sorted=False

Function LinInterpolate(xVal As Double, _
xRange As Variant, _
yRange As Variant, _
Optional isSorted As Long = 1) As Double
Dim yVal As Double
Dim xBelow As Double, xAbove As Double
Dim yBelow As Double, yAbove As Double
Dim testVal As Double
Dim High As Long, Med As Long, Low As Long

Low = 1
High = xRange.Cells.Count

If isSorted <> 0 Then
Rem binary search sorted range
Do
Med = Int((Low + High) \ 2)
If (xRange.Cells(Med).Value) < (xVal) Then
Low = Med
Else
High = Med
End If
Loop Until Abs(High - Low) <= 1
Else
Rem search every entry
xBelow = -1e+205
xAbove = 1e+205

For Med = 1 To xRange.Cells.Count
testVal = xRange.Cells(Med)
If testVal < xVal Then
If Abs(xVal - testVal) < Abs(xVal - xBelow) Then
Low = Med
xBelow = testVal
End If
Else
If Abs(xVal - testVal) < Abs(xVal - xAbove) Then
High = Med
xAbove = testVal
End If
End If
Next Med
End If

xBelow = xRange.Cells(Low): xAbove = xRange.Cells(High)
yBelow = yRange.Cells(Low): yAbove = yRange.Cells(High)

LinInterpolate = yBelow + (xVal - xBelow) * (yAbove - yBelow) / (xAbove - xBelow)
End Function

[Trine.engr]

Thanks mikerickson, that works perfect for what I want. i think i was looking at it too basic, but i think yours will be more reliable and effective.

[sinkon]

Something very similar can be done with native excel (formulas and/or Named functions).<br>
<br>
But it sounds like you need a UDF.<br>
<br>
Thank you a lot! The excel macro is of great help.

[exp]

Thanks for sharing!


You might use this.
The Sorted argument should be True (the default) if the range with the x values is sorted either ascending or descending. An unordered x Range should have Sorted=False

Function LinInterpolate(xVal As Double, _
xRange As Variant, _
yRange As Variant, _
Optional isSorted As Long = 1) As Double
Dim yVal As Double
Dim xBelow As Double, xAbove As Double
Dim yBelow As Double, yAbove As Double
Dim testVal As Double
Dim High As Long, Med As Long, Low As Long

Low = 1
High = xRange.Cells.Count

If isSorted <> 0 Then
Rem binary search sorted range
Do
Med = Int((Low + High) \ 2)
If (xRange.Cells(Med).Value) < (xVal) Then
Low = Med
Else
High = Med
End If
Loop Until Abs(High - Low) <= 1
Else
Rem search every entry
xBelow = -1e+205
xAbove = 1e+205

For Med = 1 To xRange.Cells.Count
testVal = xRange.Cells(Med)
If testVal < xVal Then
If Abs(xVal - testVal) < Abs(xVal - xBelow) Then
Low = Med
xBelow = testVal
End If
Else
If Abs(xVal - testVal) < Abs(xVal - xAbove) Then
High = Med
xAbove = testVal
End If
End If
Next Med
End If

xBelow = xRange.Cells(Low): xAbove = xRange.Cells(High)
yBelow = yRange.Cells(Low): yAbove = yRange.Cells(High)

LinInterpolate = yBelow + (xVal - xBelow) * (yAbove - yBelow) / (xAbove - xBelow)
End Function