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?

 

[TheControlsFreak]

Your table looks like it is always increasing in z - maybe I am reading it wrong?

Ok, after re-looking at the values... I will agree they are increasing based on the neighboring values of any one Z point.

Can you fix the formatting and post the lookup you are trying with the result you are getting?

Result Formula:
=InvBilinearInterpolation($B$11:$N$11 , $A$13:$A$23 , $B$13:$N$23 , $C$2 , "x" , $C$3)

$C$2 = Known Pressure
$C$3 = Known RPM

[TABLE="width: 896"]
<tbody>[TR]
[TD="colspan: 2"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Pigankle]

And what vales are you using for "Known pressure" and "Known RPM" to get an interpolated Air Volume that seems incorrect? What is the result you are getting vs. the result you expect??

 

[jwolter]

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?

I don't think your problem is tied to the value of one in your top row. I think it is tied to the fact that your table isn't filled in. Try putting in ridiculous values (e.g. 9900,9901,etc.) into the blank part of your table and see if that works.

 

[TheControlsFreak]

I don't think your problem is tied to the value of one in your top row. I think it is tied to the fact that your table isn't filled in. Try putting in ridiculous values (e.g. 9900,9901,etc.) into the blank part of your table and see if that works.

Thanks JW... that fixed it.

I had dismissed that as a possibility since the calculations were off even when selecting a point where the neighboring cells had values, but I guess I was missing some of the calcs in the VBA where it uses the full cell range in the calculations as opposed to just the filled in numbers.

Just what I was looking for.

 

[Swapnil65]

I don't think your problem is tied to the value of one in your top row. I think it is tied to the fact that your table isn't filled in. Try putting in ridiculous values (e.g. 9900,9901,etc.) into the blank part of your table and see if that works.

Thanks a Lot for nice solution and code for Reverse Interpolation ! I am working on same type of problem and wanted to avoid multiple looping and coding ! This is just marvelous !

I found that the Z values can not be empty ! At the same time it must not repeat also with same value in one row when they have reached their maximum value ! I think at such time, we must put some values, practically identical but mathematically changing in third decimal, to ' deceive" the code mathematically and get practically same answer !

Is there any way out, to change code, for x/y reached maximum ?

(Of Course, I have option of previously checking this and add in subroutine based on this function.)

Thanks once again !