Reverse Interpolate on 2D table

[Pigankle]

Hello -

I have a family of curves representing flow through a gate with various elevations and head. My table is designed like so:
<table border = "1">
<tr>
<th></th>
<th>1 ft</th>
<th>2 ft</th>
<th>3 ft</th>
<th>4 ft</th>
<th>5 ft</th>
</tr>
<tr>
<td>100</td>
<td>1 cfs</td>
<td>2 cfs</td>
<td>4 cfs</td>
<td> 7 cfs</td>
<td>11 cfs</td>
</tr>
<tr>
<td>200 </td>
<td>2 cfs</td>
<td>3.5 cfs</td>
<td>7 cfs</td>
<td>14 cfs</td>
<td>23 cfs</td>
</tr>
<tr>
<td>300</td>
<td>4 cfs</td>
<td>9 cfs</td>
<td>15 cfs</td>
<td>29 cfs</td>
<td>48 cfs</td>
</tr>
<tr>
<td>400</td>
<td>8 cfs</td>
<td>16 cfs</td>
<td>32 cfs</td>
<td>56 cfs</td>
<td>91 cfs </td>
</tr>
<tr>
<td>500</td>
<td>16 cfs</td>
<td>32 cfs</td>
<td>64 cfs</td>
<td>112 cfs</td>
<td>176 cfs</td>
</tr>
</table>


Various elevations are in the rows and various gate openings are in the columns - the cells in the middle show corresponding flows. Things to note:

• Across each row and down each column, the values are strictly increasing.
• The functions across each row and down each column are non-linear, but my datapoints are dense (50x50) , and can be assumed to be linear locally
• I am putting in the units to make it easier to understand the table, but my table is really only numeric.

I have written an interpolation function that can use an elevation and gates setting and interpolate between to get a flow. It works. I would also like a function that can use an elevation and desired flow and do an inverse lookup to find the appropriate gate setting - something like:

reverseinterp(350 ft, 9.25 cfs) = 1.5 ft

I see a path to do this in vba, but it involves lots of loops and temporary arrays and will be a pain to write - I guess I am hoping that someone out there has already done this and would be willing to share.

Anyone?

 

[shg]

I can help you do this with a forward interpolator and Solver:

      -A- -B-- -C-- -D-- --E-- --F-- G -H-- -------------I--------------
  1          1    2    3     4     5   1.50 H1: Input                   
  2   100  1.0  2.0  4.0   7.0  11.0   9.25 H2: =BiLInterp(H1,350,A1:F6)
  3   200  2.0  3.5  7.0  14.0  23.0                                    
  4   300  4.0  9.0 15.0  29.0  48.0                                    
  5   400  8.0 16.0 32.0  56.0  91.0                                    
  6   500 16.0 32.0 64.0 112.0 176.0

The UDF is in H2, and I used Solver to make its value 9.25 by changing H1.

If that works for you, I'll post the UDF.

 

[Pigankle]

Appreciate the offer - but I need a standalone function. I will be using it on several thousand lookups, each dependent on the outcome of the previous one, so re-targetting solver is not feasible....

Basically I am asking a question (of excel ) along the lines of: suppose I want to drain a container at a specific rate - how do I need to adjust the outflow orifice to maintain that rate?

At each timestep, I lookup the elevation that resulted from the previous timestep's drainage, and use that with my drainage rate to find a new orifice opening.....This is complicated by the desired rate not being quite constant, so I have to allow that to be part of the lookup too.....

So I have columns like this:
Discharge Orifice Storage Elevation

Where , in each row
Discharge => flexible (varies, I am trying out different scenarios)
Orifice => the output of the function I am after, with the previous elevation and current discharge
Storage => Previous storage minus this discharge
Elevation => lookup from storage

There will be ~4000 timesteps

 

[jwolter]

I know this is way after the fact, but I needed something like this as well, so I wrote one. The code uses some pieces from a bilinear interpolation routine that I got from the web, but unfortunately I don't have the information to acknowledge the previous author. So in the spirit of giving back to the community, here it is:

Public Function InvBilinearInterpolation(xRange As Range, yrange As Range, zRange As Range, xyCoord As Double, xyPick As String, zCoord As Double) As Double
' This is a reverse bilinear interpolation.  A common application of 2D tables is
' to interpolate a z value in the body of the table from x and y values in the
' table headers.  This routine reverses that, interpolating an x or y value given
' the other (y or x) value and the z value.  Note that this rotine will give
' ambiguous results if the values of z do not monotonically increase or decrease
' in the unknown axis.
    Dim i As Integer
    Dim n1 As Integer
    Dim n2 As Integer
    Dim nx As Integer
    Dim ny As Integer
    Dim swap As Boolean
    Dim temp() As Double
    Dim xAxis As Variant
    Dim yAxis As Variant
    Dim zSurface As Variant
    '
    Application.StatusBar = ""
    n1 = xRange.Rows.Count
    n2 = xRange.Columns.Count
    If n1 > 1 And n2 = 1 Then
        xAxis = xRange.Value2
        nx = n1
    ElseIf n1 = 1 And n2 > 1 Then
        xAxis = Application.WorksheetFunction.Transpose(xRange.Value2)
        nx = n2
    Else
        Application.StatusBar = "Error - xRange should be a one-dimensional array of at least 2 cells."
        InvBilinearInterpolation = 0
        Exit Function
    End If
    n2 = yrange.Columns.Count
    n1 = yrange.Rows.Count
    If n1 > 1 And n2 = 1 Then
        yAxis = yrange.Value2
        ny = n1
    ElseIf n1 = 1 And n2 > 1 Then
        yAxis = Application.WorksheetFunction.Transpose(yrange.Value2)
        ny = n2
    Else
        Application.StatusBar = "Error - yRange should be a one-dimensional array of at least 2 cells."
        InvBilinearInterpolation = 0
        Exit Function
    End If
    zSurface = zRange.Value2
    n2 = zRange.Columns.Count
    n1 = zRange.Rows.Count
    If n1 = ny And n2 = nx Then
        swap = True
    ElseIf n1 = nx And n2 = ny Then
        swap = False
    Else
        Application.StatusBar = "Error - zRange dimensions do not match xRange and yRange dimensions."
        InvBilinearInterpolation = 0
        Exit Function
    End If
    If LCase(xyPick) = "x" Then
        If Not swap Then
            InvBilinearInterpolation = GetInvBilinearInterpolation(xAxis, yAxis, zSurface, xyCoord, "x", zCoord)
        Else
            InvBilinearInterpolation = GetInvBilinearInterpolation(yAxis, xAxis, zSurface, xyCoord, "y", zCoord)
        End If
    ElseIf LCase(xyPick) = "y" Then
        If Not swap Then
            InvBilinearInterpolation = GetInvBilinearInterpolation(xAxis, yAxis, zSurface, xyCoord, "y", zCoord)
        Else
            InvBilinearInterpolation = GetInvBilinearInterpolation(yAxis, xAxis, zSurface, xyCoord, "x", zCoord)
        End If
    Else
        Application.StatusBar = "Error - xypick must be either ""x"" or ""y""."
        InvBilinearInterpolation = 0
        Exit Function
    End If
End Function
Public Function GetInvBilinearInterpolation(xAxis As Variant, yAxis As Variant, zSurface As Variant, xyCoord As Double, xyPick As String, zCoord As Double) As Double
  
  'first find 4 neighbouring points
  nx = UBound(xAxis, 1)
  ny = UBound(yAxis, 1)
  
  Dim lx As Single 'index of x coordinate of adjacent grid point to left of P
  Dim ux As Single 'index of x coordinate of adjacent grid point to right of P
  
  Dim ly As Single  'index of y coordinate of adjacent grid point below P
  Dim uy As Single  'index of y coordinate of adjacent grid point above P
  Dim zLine() As Double
  Dim xyLine() As Double
  
  Application.StatusBar = ""
  If LCase(xyPick) = "x" Then
    GetNeigbourIndices xAxis, xyCoord, lx, ux
    ReDim xyLine(1 To ny)
    ReDim zLine(1 To ny)
    If lx = ux Then
        For i = 1 To ny
            xyLine(i) = yAxis(i, 1)
            zLine(i) = zSurface(lx, i)
        Next i
    Else
        x = xyCoord
        X1 = xAxis(lx, 1)
        X2 = xAxis(ux, 1)
        For i = 1 To ny
            xyLine(i) = yAxis(i, 1)
            zLine(i) = (x - X1) / (X2 - X1) * zSurface(lx, i) + _
                   (X2 - x) / (X2 - X1) * zSurface(ux, i)
        Next i
    End If
    GetInvBilinearInterpolation = Tablinterp(zLine, xyLine, zCoord)
  ElseIf LCase(xyPick) = "y" Then
    GetNeigbourIndices yAxis, xyCoord, ly, uy
    ReDim xyLine(1 To nx)
    ReDim zLine(1 To nx)
    If ly = uy Then
        For i = 1 To nx
            xyLine(i) = xAxis(i, 1)
            zLine(i) = zSurface(i, ly)
        Next i
    Else
        y = xyCoord
        Y1 = yAxis(lx, 1)
        Y2 = yAxis(ux, 1)
        For i = 1 To nx
            xyLine(i) = xAxis(i, 1)
            zLine(i) = (y - Y1) / (Y2 - Y1) * zSurface(i, ly) + _
                   (Y2 - y) / (Y2 - Y1) * zSurface(i, uy)
        Next i
    End If
    GetInvBilinearInterpolation = Tablinterp(zLine, xyLine, zCoord)
  Else
    Application.StatusBar = "Error - xypick must be either ""x"" or ""y""."
    GetInvBilinearInterpolation = 0
  End If
  
End Function

Public Sub GetNeigbourIndices(inArr As Variant, x As Double, ByRef lowerX As Single, ByRef upperX As Single)
 N = UBound(inArr, 1)
 If x <= inArr(1, 1) Then
    lowerX = 1
    upperX = 1
  ElseIf x >= inArr(N, 1) Then
    lowerX = N
    upperX = N
  Else
    For i = 2 To N
      If x < inArr(i, 1) Then
        lowerX = i - 1
        upperX = i
        Exit For
      ElseIf x = inArr(i, 1) Then
        lowerX = i
        upperX = i
        Exit For
      End If
    Next i
  End If
End Sub

Function Tablinterp(xVals, yVals, xP)
 Tablinterp = CVErr(xlErrNA)
 nx = UBound(xVals)
 For i = 1 To nx - 1
  If (xP - xVals(i)) * (xP - xVals(i + 1)) <= 0 Then
   Tablinterp = Linterp(xVals(i), xVals(i + 1), xP, yVals(i), yVals(i + 1))
  End If
 Next i
End Function

Function Linterp(X1, X2, xP, Y1, Y2) As Variant
'   See also: Function Tablinterp
' Linear interpolation.  Given two coordinate pairs (X1, Y1) and (X2, Y2),
' interpolates a value of YP for a given XP.
' XP must be between X1 and X2, or this function will return "N/A"
If (xP - X1) * (xP - X2) > 0 Then
 Linterp = CVErr(xlErrNA)
ElseIf (xP = X1) Then
 Linterp = Y1
Else
 Linterp = (Y2 - Y1) * (xP - X1) / (X2 - X1) + Y1
End If
End Function

 

[Pigankle]

I know this is way after the fact, but I needed something like this as well, so I wrote one. The code uses some pieces from a bilinear interpolation routine that I got from the web, but unfortunately I don't have the information to acknowledge the previous author. So in the spirit of giving back to the community, here it is:

Very cool to see a post get some attention after so long! Double very cool! Awesome!

Hoping for a little help with trying out your function. Suppose this is my data structure:
[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Orifice
[/TD]
[TD]Opening[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]0
[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Elevation[/TD]
[TD]1070
[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]1080[/TD]
[TD]0[/TD]
[TD]150[/TD]
[TD]300[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]1090[/TD]
[TD]0[/TD]
[TD]200[/TD]
[TD]400[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]1100[/TD]
[TD]0[/TD]
[TD]250[/TD]
[TD]500[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]1110
[/TD]
[TD]0[/TD]
[TD]300[/TD]
[TD]600[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]1120[/TD]
[TD]0[/TD]
[TD]350[/TD]
[TD]700[/TD]
[TD]2000[/TD]
[/TR]
</tbody>[/TABLE]

I can use your function to answer the question:

"What orifice setting do I need in order to pass 500 cfs if the elevation is 1115 ft?"

=InvBilinearInterpolation($C$2:$F$2,$B$3:$B$8,$C$3:$F$8,1115,"y",500)

Result = 1.54

How about "At what elevation will a gate setting of 1.8 pass exactly 500 cfs?"
I thought that I would change xyPick to "x" and put in "1.8" as xyCoord

=InvBilinearInterpolation($C$2:$F$2,$B$3:$B$8,$C$3:$F$8,1.8,"x",500)

But that returns an error. What am I doing wrong?

 

[jwolter]

What am I doing wrong?

You aren't doing anything wrong, but thank you for participating in the jwolter macro beta test program! I should have tested my routine in all directions, but I missed one. Here's the corrected code:

Public Function InvBilinearInterpolation(xRange As Range, yrange As Range, zRange As Range, xyCoord As Double, xyPick As String, zCoord As Double) As Double
' This is a reverse bilinear interpolation.  A common application of 2D tables is
' to interpolate a z value in the body of the table from x and y values in the
' table headers.  This routine reverses that, interpolating an x or y value given
' the other (y or x) value and the z value.  Note that this rotine will give
' ambiguous results if the values of z do not monotonically increase or decrease
' in the unknown axis.
    Dim i As Integer
    Dim n1 As Integer
    Dim n2 As Integer
    Dim nx As Integer
    Dim ny As Integer
    Dim swap As Boolean
    Dim temp() As Double
    Dim xAxis As Variant
    Dim yAxis As Variant
    Dim zSurface As Variant
    '
    Application.StatusBar = ""
    n1 = xRange.Rows.Count
    n2 = xRange.Columns.Count
    If n1 > 1 And n2 = 1 Then
        xAxis = xRange.Value2
        nx = n1
    ElseIf n1 = 1 And n2 > 1 Then
        xAxis = Application.WorksheetFunction.Transpose(xRange.Value2)
        nx = n2
    Else
        Application.StatusBar = "Error - xRange should be a one-dimensional array of at least 2 cells."
        InvBilinearInterpolation = 0
        Exit Function
    End If
    n2 = yrange.Columns.Count
    n1 = yrange.Rows.Count
    If n1 > 1 And n2 = 1 Then
        yAxis = yrange.Value2
        ny = n1
    ElseIf n1 = 1 And n2 > 1 Then
        yAxis = Application.WorksheetFunction.Transpose(yrange.Value2)
        ny = n2
    Else
        Application.StatusBar = "Error - yRange should be a one-dimensional array of at least 2 cells."
        InvBilinearInterpolation = 0
        Exit Function
    End If
    zSurface = zRange.Value2
    n2 = zRange.Columns.Count
    n1 = zRange.Rows.Count
    If n1 = ny And n2 = nx Then
        swap = True
    ElseIf n1 = nx And n2 = ny Then
        swap = False
    Else
        Application.StatusBar = "Error - zRange dimensions do not match xRange and yRange dimensions."
        InvBilinearInterpolation = 0
        Exit Function
    End If
    If LCase(xyPick) = "x" Then
        If Not swap Then
            InvBilinearInterpolation = GetInvBilinearInterpolation(xAxis, yAxis, zSurface, xyCoord, "x", zCoord)
        Else
            InvBilinearInterpolation = GetInvBilinearInterpolation(yAxis, xAxis, zSurface, xyCoord, "y", zCoord)
        End If
    ElseIf LCase(xyPick) = "y" Then
        If Not swap Then
            InvBilinearInterpolation = GetInvBilinearInterpolation(xAxis, yAxis, zSurface, xyCoord, "y", zCoord)
        Else
            InvBilinearInterpolation = GetInvBilinearInterpolation(yAxis, xAxis, zSurface, xyCoord, "x", zCoord)
        End If
    Else
        Application.StatusBar = "Error - xypick must be either ""x"" or ""y""."
        InvBilinearInterpolation = 0
        Exit Function
    End If
End Function
Public Function GetInvBilinearInterpolation(xAxis As Variant, yAxis As Variant, zSurface As Variant, xyCoord As Double, xyPick As String, zCoord As Double) As Double
  
  'first find 4 neighbouring points
  nx = UBound(xAxis, 1)
  ny = UBound(yAxis, 1)
  
  Dim lx As Single 'index of x coordinate of adjacent grid point to left of P
  Dim ux As Single 'index of x coordinate of adjacent grid point to right of P
  
  Dim ly As Single  'index of y coordinate of adjacent grid point below P
  Dim uy As Single  'index of y coordinate of adjacent grid point above P
  Dim zLine() As Double
  Dim xyLine() As Double
  
  Application.StatusBar = ""
  If LCase(xyPick) = "x" Then
    GetNeigbourIndices xAxis, xyCoord, lx, ux
    ReDim xyLine(1 To ny)
    ReDim zLine(1 To ny)
    If lx = ux Then
        For i = 1 To ny
            xyLine(i) = yAxis(i, 1)
            zLine(i) = zSurface(lx, i)
        Next i
    Else
        x = xyCoord
        X1 = xAxis(lx, 1)
        X2 = xAxis(ux, 1)
        For i = 1 To ny
            xyLine(i) = yAxis(i, 1)
            zLine(i) = (X2 - x) / (X2 - X1) * zSurface(lx, i) + _
                   (x - X1) / (X2 - X1) * zSurface(ux, i)
        Next i
    End If
    GetInvBilinearInterpolation = Tablinterp(zLine, xyLine, zCoord)
  ElseIf LCase(xyPick) = "y" Then
    GetNeigbourIndices yAxis, xyCoord, ly, uy
    ReDim xyLine(1 To nx)
    ReDim zLine(1 To nx)
    If ly = uy Then
        For i = 1 To nx
            xyLine(i) = xAxis(i, 1)
            zLine(i) = zSurface(i, ly)
        Next i
    Else
        y = xyCoord
        Y1 = yAxis(ly, 1)
        Y2 = yAxis(uy, 1)
        For i = 1 To nx
            xyLine(i) = xAxis(i, 1)
            zLine(i) = (Y2 - y) / (Y2 - Y1) * zSurface(i, ly) + _
                   (y - Y1) / (Y2 - Y1) * zSurface(i, uy)
        Next i
    End If
    GetInvBilinearInterpolation = Tablinterp(zLine, xyLine, zCoord)
  Else
    Application.StatusBar = "Error - xypick must be either ""x"" or ""y""."
    GetInvBilinearInterpolation = 0
  End If
  
End Function

Public Sub GetNeigbourIndices(inArr As Variant, x As Double, ByRef lowerX As Single, ByRef upperX As Single)
 N = UBound(inArr, 1)
 If x <= inArr(1, 1) Then
    lowerX = 1
    upperX = 1
  ElseIf x >= inArr(N, 1) Then
    lowerX = N
    upperX = N
  Else
    For i = 2 To N
      If x < inArr(i, 1) Then
        lowerX = i - 1
        upperX = i
        Exit For
      ElseIf x = inArr(i, 1) Then
        lowerX = i
        upperX = i
        Exit For
      End If
    Next i
  End If
End Sub

Function Tablinterp(xVals, yVals, xP)
 Tablinterp = CVErr(xlErrNA)
 nx = UBound(xVals)
 For i = 1 To nx - 1
  If (xP - xVals(i)) * (xP - xVals(i + 1)) <= 0 Then
   Tablinterp = Linterp(xVals(i), xVals(i + 1), xP, yVals(i), yVals(i + 1))
  End If
 Next i
End Function

Function Linterp(X1, X2, xP, Y1, Y2) As Variant
'   See also: Function Tablinterp
' Linear interpolation.  Given two coordinate pairs (X1, Y1) and (X2, Y2),
' interpolates a value of YP for a given XP.
' XP must be between X1 and X2, or this function will return "N/A"
If (xP - X1) * (xP - X2) > 0 Then
 Linterp = CVErr(xlErrNA)
ElseIf (xP = X1) Then
 Linterp = Y1
Else
 Linterp = (Y2 - Y1) * (xP - X1) / (X2 - X1) + Y1
End If
End Function

 

[jwolter]

By the way, I am totally shocked that you are still, after almost 3 years, monitoring this thread and interested in this problem. I was going for the "it's too late for the OP, but maybe someone else will find this useful" angle. I'm thrilled if this is useful to you!

 

[Pigankle]

It has been a while....I made a workaround - a matlab script that loops through the tables and does a whole bunch of interpolation to create inverted versions that I can then use my forward bilinear interpolation vba function on.
That approach works fine, but requires that I keep three copies of each lookup table in the workbook, one for each possible lookup variable.
The function you made closes the gap, though, and now I only need one table!

BTW - in case anyone else stumbles on this thread:

• xrange are the column headers
• yrange are the row headers
• zcoord is a known value out in the middle of the table
• xycoord is a known value from either the row headers range or the column headers range
• Use xyPick = "y" if the row header value is known and you are interpolating within column headers.
• Use xyPick = "x" if the column header value is known and you are interpolating within row headers.

Thanks again!

 

[TheControlsFreak]

Public Function InvBilinearInterpolation(xRange As Range, yrange As Range, zRange As Range, xyCoord As Double, xyPick As String, zCoord As Double) As Double
' This is a reverse bilinear interpolation.  A common application of 2D tables is
' to interpolate a z value in the body of the table from x and y values in the
' table headers.  This routine reverses that, interpolating an x or y value given
' the other (y or x) value and the z value.  Note that this rotine will give
' ambiguous results if the values of z do not monotonically increase or decrease
' in the unknown axis.

So I need to ask the question... what changes would need to be implemented if Z does not continue to increase 'monotonically'? in the original Z values?

OR

Is my problem due to the fact that my top (x) value bridges <> 1?

    RPM                Pressure
	        0.2	0.4	0.6	0.8	1.0	
    CFM											
    3750      709	747	787	831	880	
    4000	725	762	802	846	896	 
    4250	742	779	819	863	914
    4500	759	796	836	882	934	
    4750	777	814	855	901	954	
    5000	796	833	875	922	974

The above values represent the top left quadrant of the full table of values. If I solve for any CFM values with pressures of 0.2 to 1.0 the functions works PERFECTLY!

But once I choose a pressure that is 1.4 or 2.6 then the numbers go WAY OFF.

                1.0      1.2     1.4     1.6     1.8     2.0
3750		935	  990	   1038   1081   1125   1170
4000		952	  1006   1052  1095    1138   1183	
4250		970	  1021   1066  1109    1152   1197	
4500		988	  1037   1080  1123    1167   1212	
4750		1007   1053	   1095  1138    1182	
5000		1024   1069	   1111  1154    1197	
5250		1042   1085	   1127  1170    1213			
5500		1060   1102	   1144  1186				
5750		1078   1119						
6000		1053   1095							
6250		1072

 

[Pigankle]

Your table looks like it is always increasing in z - maybe I am reading it wrong? (Your formatting is a little goofed up.) Can you fix the formatting and post the lookup you are trying with the result you are getting?