Interpolation of a table

[timberscombe]

Hi Mr. Excel Forum - I'm wondering if anyone can help me please? I'm pulling out what little hair I have left in my head! I'm sorry but this is a re-post as no one was able to help last time and I'm pretty desperate to know if this is possible!

Basically I have a table of results and I am trying to use Excel to calculate the exact value I need by a means of double interpolation of this table.
I have read a few posts on here with people having similar queries but this is slightly different from those I've read so far....

Here is a sample of the table that I'm looking to interpolate:

.......5....10..15...20
180 221 166 133 112
185 219 163 130 109
190 217 161 127 107
195 215 158 125 104
200 213 156 122 102
210 209 151 118 98
220 206 147 114 94

The values along the top (5,10,15,20) are relating to values of dx
The values along the side (180,185,190,195,200,210,220) are LE values

For arguements sake, say I have calculated values dx = 12.8 and LE = 196.2

Is there anyway I can get Excel to interpolate the table and calculate what the correct corresponding value is?

Thank you for taking the time to read this, any assistance will be greatly appreciated.

 

[shg]

Option Explicit
 
Public Function BiLInterp(x As Double, _
                          y As Double, _
                          rTbl As Range) As Variant
    ' UDF wrapper for BiLInterp
 
    Dim rX          As Range    ' top row of rTbl
    Dim rY          As Range    ' left column of rTbl
    Dim avdX        As Variant
    Dim avdY        As Variant
    Dim avdZ        As Variant
 
    On Error GoTo Oops
    If rTbl.Areas.Count > 1 Then Err.Raise xlErrValue
    If rTbl.Rows.Count < 3 Or rTbl.Columns.Count < 3 Then Err.Raise xlErrValue
 
    With WorksheetFunction
        If .Count(rTbl) - .Count(rTbl(1)) <> rTbl.Cells.Count - 1 Then Err.Raise xlErrValue
        Set rX = Range(rTbl(1, 2), rTbl(1, rTbl.Columns.Count))
        Set rY = Range(rTbl(2, 1), rTbl(rTbl.Rows.Count, 1))
        avdX = .Transpose(.Transpose(rX.Value))
        avdY = .Transpose(rY.Value)
        avdZ = Range(rTbl(2, 2), rTbl(rTbl.Rows.Count, rTbl.Columns.Count)).Value2
        BiLInterp = dBiLInterp(x, y, avdX, avdY, avdZ)
    End With
    Exit Function
 
Oops:
    BiLInterp = CVErr(Err.Number)
End Function
 
Function dBiLInterp(x As Double, y As Double, _
                    avdX As Variant, avdY As Variant, _
                    avdZ As Variant) As Variant
    ' shg 1997-0606, 2007-0307, 2009-0419
    '     2009-0615 added support for ascending or descending sort
    '     2009-1021 changed implementation of weighted sum
    ' Returns the bilinear interpolation of rTbl with
    '   o   x interpolated across the top row (rX)
    '   o   y interpolated down the left column (rY)
    ' x and y must be within the upper and lower limits of rX and rY
    ' rTbl must be sorted
    '   o    left to right by the top row
    '   o    top to bottom by the left column
    ' The sort orders can be ascending or descending,
    ' each independent of the other.
    ' All values in rTbl must be numeric (other than the UL corner,
    ' which is ignored).
    Dim iRow        As Long
    Dim iCol        As Long
    Dim dRF         As Double   ' row fraction
    Dim dCF         As Double   ' column fraction
    Frac x, avdX, iCol, dCF, IIf(avdX(UBound(avdX)) > avdX(1), 1, -1)
    Frac y, avdY, iRow, dRF, IIf(avdY(UBound(avdY)) > avdX(1), 1, -1)
    
    ' weighted sum of four corners
    dBiLInterp = avdZ(iRow + 0, iCol + 0) * (1# - dRF) * (1# - dCF) + _
                 avdZ(iRow + 0, iCol + 1) * (1# - dRF) * (dCF - 0#) + _
                 avdZ(iRow + 1, iCol + 0) * (dRF - 0#) * (1# - dCF) + _
                 avdZ(iRow + 1, iCol + 1) * (dRF - 0#) * (dCF - 0#)
End Function
 
Private Function Frac(d As Double, _
                      avd As Variant, _
                      ByRef i As Long, _
                      ByRef dF As Double, _
                      iMatchType As Long)
    ' shg 1997-0606, 2009-0419
    '     2009-0604 added option for descending sort
    ' Returns an index to avd in i and an interpolation fraction in dF
    ' avd must be a 2+ element vector sorted {a|de}scending if iMatchType={1|-1}
    If iMatchType = 1 And d <= avd(1) Or _
       iMatchType = -1 And d >= avd(1) Then
        i = 1
    Else
        ' this can generate an error, handled by caller
        i = WorksheetFunction.Match(d, avd, iMatchType)
        If i = UBound(avd) Then i = UBound(avd) - 1
    End If
    dF = (d - avd(i)) / (avd(i + 1) - avd(i))
End Function

E.g., for your example data, =BiLinterp(12.8, 196.2, A1:E8) returns 138.9

 

[timberscombe]

Thank you so much for your assistance with this shg!
To be honest with you I have never heard of the "BiLinterp" function.
But now you have shown me this function, I think it is going to help me so much.

I can't thank you enough!!!

 

[shg]

It's just straight bilinear interpolation -- you're very welcome, glad it worked for you.

 

[specialk802]

stupid question but how do you go about implementing the vba code above and using the function? I copy and pasted the code into VBA but am not really sure what to do next.

 

[specialk802]

Nevermind figured it out

stupid question but how do you go about implementing the vba code above and using the function? I copy and pasted the code into VBA but am not really sure what to do next.