Linear Interpolation in excel

J

JohnJack

Hello again,

I have written the following function as I cannot find a function in
excel that works properly. This linearly interpolates a value in the
2nd range (y1), by finding the the position of the two values in the
first range (y0) that surround (above and below) the "x" value which
can be a double. The problem is is that this function really slows
down the spreadsheet (it does work), but I was wonder if anyone could
point out places where I could possibly speed it up.

Function LinTerp(y0 As Range, y1 As Range, x As Double)
'this function will return the linear interpulated value corresponding
to the x value found in the y0 range (y0 range has to be in ascending
order)
'the range y0 has cannot be sinusoidal or parabolic as it will find
the first instance in the range
'that surrounds the value being searched for.

Dim VL As Double
Dim y0Num, y1Num, vlpos As Integer
Dim y0Below, y0Above, y1Below, y1Above, A, B, C As Double

y0Num = Application.Count(y0)
y1Num = Application.Count(y1)

If y0Num <> y1Num Then 'if the two ranges are not of the same
length, this function should stop as it will calculate the wrong
number
LinTerp = "Ranges Inconsistant"
Else
VL = Application.VLookup(x, y0, 1, True)

'binary search to find the position of VL (Vlpos) in y0 range
Dim foundFlag As Boolean
Dim first, middle, last As Integer
foundFlag = False

first = 1
last = y0Num

Do While (first <= last) And (Not foundFlag)
vlpos = Int((first + last) / 2)
Select Case y0(vlpos, 1)
Case VL
foundFlag = True
Case Is > VL
last = vlpos - 1
Case Is < VL
first = vlpos + 1
End Select
Loop

'end of binary search

y0Below = y0(vlpos, 1)
y0Above = y0(vlpos + 1, 1)
y1Below = y1(vlpos, 1)
y1Above = y1(vlpos + 1, 1)

A = y0Above - y0Below
B = x - y0Below
C = B / A

LinTerp = y1Below + (y1Above - y1Below) * C
End If

End Function
 
T

Tom Ogilvy

Assuming your data is linear,

Use the Forecast function.

More powerful is the LINEST

also slope, intercept and trend functions.

If you actually want to interpolate just the values before and after, then
you can contruct a more complex formula

Table in A1:B10, Value to interpolate in C1. Data sorted as your function
requires.

=TREND(OFFSET(B1,MATCH(C1,A1:A10)-1,0,2,1),
OFFSET(A1,MATCH(C1,A1:A10)-1,0,2,1),C1)
 
J

JohnJack

Again Tom, you're a god. Thanks so much.

Jack

Assuming your data is linear,

Use the Forecast function.

More powerful is the LINEST

also slope, intercept and trend functions.

If you actually want to interpolate just the values before and after, then
you can contruct a more complex formula

Table in A1:B10, Value to interpolate in C1. Data sorted as your function
requires.

=TREND(OFFSET(B1,MATCH(C1,A1:A10)-1,0,2,1),
OFFSET(A1,MATCH(C1,A1:A10)-1,0,2,1),C1)
 
D

dgp

Assuming your data is linear,

Use the Forecast function.

More powerful is the LINEST

also slope, intercept and trend functions.

If you actually want to interpolate just the values before and after, then
you can contruct a more complex formula

Table in A1:B10, Value to interpolate in C1. Data sorted as your function
requires.

=TREND(OFFSET(B1,MATCH(C1,A1:A10)-1,0,2,1),
OFFSET(A1,MATCH(C1,A1:A10)-1,0,2,1),C1)

Very cool interpolation function Tom!
 
T

Tom Ogilvy

Credit goes to Harlan Grove.

(although I am sure I have written something similar in the past <g>)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top