Linear Interpolation, one variable, Excel

[BruceMutton]

Further to threads thread770-14670 and Thread770-13682

A rather verbose, but comprehensive user defined function follows, and can be pasted directly from here, into your spreadsheet.

If anyone has bothered to do one for two variables, I would appreciate a copy.

Thanks Bruce Mutton

Option Explicit

Public Function LInterpolateVLOOKUP(sLookup_value As Single, rTable_array As Range, iCol_index_num As Integer)
'A User Defined Function that returns values linearly interpolated "between"
'the points in the lookup table. May also return error values, hence definition as implied Variant

'Created by Myrna Larson
'obtained from Mr Excel by Bruce Mutton July 2001
'Substantially edited and documented by Bruce Mutton July 2001

'I have tryed to emulate the behaviour of builtin VLookup function
'sLookup_value is the value to be found in the first column of the array.
' sLookup_value can be a value or a reference
'rTable_array is the table of information in which data is looked up.
' Use a reference to a range or a range name, such as Database or List.
' Values in the first column of table_array must be placed in ASCENDING ORDER:
' ..., -2, -1, 0, 1, 2, ... , otherwise LInterpolateVLOOKUP
' WILL NOT give the correct value.
'iCol_index_num is the column number in rTable_array from which the matching value should be
' returned. A iCol_index_num of 1 returns the value in the first column in table_array;
' a iCol_index_num of 2 returns the value in the second column in table_array, and so on.
' If iCcol_index_num is less than 1, LInterpolateVLOOKUP returns the #VALUE! error value;
' if iCol_index_num is greater than the number of columns in table_array, LInterpolateVLOOKUP returns
' the #REF! error value.
'
'If sLookup_value is smaller than the smallest value, or larger than the largestvalue, in the first
' column of rTable_array, LInterpolateVLOOKUP returns the #N/A error value.

Dim iTableRow As Integer
Dim vTemp As Variant
Dim dbl_x0 As Double, dbl_x1 As Double, dbl_yo As Double, dbl_y1 As Double

'Deal with obvious user errors
If iCol_index_num > rTable_array.Columns.Count Then
LInterpolateVLOOKUP = CVErr(xlErrRef)
Exit Function
End If

If sLookup_value < Application.WorksheetFunction.Min(rTable_array.Columns(1)) _
Or sLookup_value > Application.WorksheetFunction.Max(rTable_array.Columns(1)) Then
LInterpolateVLOOKUP = CVErr(xlErrNA)
Exit Function
End If

'Now the real thing
On Error Resume Next
vTemp = Application.WorksheetFunction.Match(sLookup_value, rTable_array.Resize(, 1), 1)
On Error GoTo 0
If IsError(vTemp) Then
LInterpolateVLOOKUP = CVErr(vTemp)

Else
iTableRow = CInt(vTemp)
dbl_x0 = rTable_array(iTableRow, 1)
dbl_yo = rTable_array(iTableRow, iCol_index_num)
If sLookup_value = dbl_x0 Then
LInterpolateVLOOKUP = dbl_yo
Else
dbl_x1 = rTable_array(iTableRow + 1, 1)
dbl_y1 = rTable_array(iTableRow + 1, iCol_index_num)
LInterpolateVLOOKUP = (sLookup_value - dbl_x0) / (dbl_x1 - dbl_x0) * (dbl_y1 - dbl_yo) + dbl_yo
End If 'sLookup_value
End If 'IsError(vTemp)
End Function

 

[Gearhead]

Ouch!!
Try using a beter tool then Excel like Matlab or TK Solver