V-look up to interpolate (simple linear interpolation)?

[samtheman88]

Hi,

I have the following table with one set of relationships.....

[TABLE="width: 364"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Stage (H)[/TD]
[TD]Outflow (O)[/TD]
[TD]Storage (S)[/TD]
[TD]2S/Δt[/TD]
[TD]2S/Δt + O[/TD]
[/TR]
[TR]
[TD]feet[/TD]
[TD]cfs[/TD]
[TD]acre-ft[/TD]
[TD]cfs[/TD]
[TD]cfs[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="align: right"]0.1[/TD]
[TD="align: right"]24.44[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]9.68[/TD]
[TD="align: right"]34.12[/TD]
[/TR]
[TR]
[TD="align: right"]0.2[/TD]
[TD="align: right"]34.57[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]19.36[/TD]
[TD="align: right"]53.93[/TD]
[/TR]
[TR]
[TD="align: right"]0.3[/TD]
[TD="align: right"]42.34[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]29.04[/TD]
[TD="align: right"]71.38[/TD]
[/TR]
[TR]
[TD="align: right"]0.4[/TD]
[TD="align: right"]48.89[/TD]
[TD="align: right"]1.6[/TD]
[TD="align: right"]38.72[/TD]
[TD="align: right"]87.61[/TD]
[/TR]
[TR]
[TD="align: right"]0.5[/TD]
[TD="align: right"]54.66[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]48.4[/TD]
[TD="align: right"]103.06[/TD]
[/TR]
[TR]
[TD="align: right"]0.6[/TD]
[TD="align: right"]59.88[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]58.08[/TD]
[TD="align: right"]117.96[/TD]
[/TR]
[TR]
[TD="align: right"]0.7[/TD]
[TD="align: right"]64.67[/TD]
[TD="align: right"]2.8[/TD]
[TD="align: right"]67.76[/TD]
[TD="align: right"]132.43[/TD]
[/TR]
[TR]
[TD="align: right"]0.8[/TD]
[TD="align: right"]69.14[/TD]
[TD="align: right"]3.2[/TD]
[TD="align: right"]77.44[/TD]
[TD="align: right"]146.58[/TD]
[/TR]
[TR]
[TD="align: right"]0.9[/TD]
[TD="align: right"]73.33[/TD]
[TD="align: right"]3.6[/TD]
[TD="align: right"]87.12[/TD]
[TD="align: right"]160.45[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]77.30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]96.8[/TD]
[TD="align: right"]174.10[/TD]
[/TR]
</tbody>[/TABLE]

Then, I have this table with another set of data...

[TABLE="width: 392"]
<colgroup><col span="5"><col></colgroup><tbody>[TR]
[TD][TABLE="width: 328"]
<colgroup><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Inflow (I0)[/TD]
[TD]2S/Δt - O[/TD]
[TD]2S/Δt + O[/TD]
[TD]Outflow (O)[/TD]
[TD]Stage (H)[/TD]
[/TR]
[TR]
[TD]cfs[/TD]
[TD]cfs[/TD]
[TD]cfs[/TD]
[TD]cfs[/TD]
[TD]feet[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD][/TD]
[TD]50[/TD]
[TD][/TD]
[TD]0.00[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

To determine the outflow in the second table, I need to interpolate for outflow (O) using the first table and my first value of 50 for 2S/Δt + O in the second table. Any ideas how to do simple linear interpolation in excel???? Someone mentioned vlookup, but I can't seem to get it to work.

 

[shg]

Unfortunately the version of excel I have on my laptop is 2003

It's a user-defined function that will work with any version of Excel.

 

[richthorpe]

Thanks shg. I've never done a user defined function before so can you give me some pointers? I presume it is something to do with adding a new module in VBA. I've managed to obtain the code from https://www.box.com/s/s0m5gs96jd3stuqa7tpu but don't really know what to do with it!
Thanks in advance for your help

It's a user-defined function that will work with any version of Excel.

 

[richthorpe]

Scrap that I've managed to figure it out!

Works a treat, thanks a lot for your help!

Thanks shg. I've never done a user defined function before so can you give me some pointers? I presume it is something to do with adding a new module in VBA. I've managed to obtain the code from https://www.box.com/s/s0m5gs96jd3stuqa7tpu but don't really know what to do with it!
Thanks in advance for your help

 

[shg]

Good job, you're welcome.

 

[pgc01]

Hi

... Linear interpolation between values is fine...

Both input sets of input values, (0.1, 2, 10, 100) and (2, 3, 4, 5, 10, 15, 20, 30, 50, 100) seem that they come out of axes with a logarithmic scale.

If that is the case, maybe you should check if a linear interpolation is adequate for what you need.

 

[richthorpe]

I'm not sure how the data was produced, but interpolation between adjacent numbers is permitted. Thanks for your input though

Hi



Both input sets of input values, (0.1, 2, 10, 100) and (2, 3, 4, 5, 10, 15, 20, 30, 50, 100) seem that they come out of axes with a logarithmic scale.

If that is the case, maybe you should check if a linear interpolation is adequate for what you need.

 

[pgc01]

You have already a solution. If you think if fits your needs then I'm glad you have your problem solved.

... interpolation between adjacent numbers is permitted....

Remark: my question is not if interpolation between adjacent numbers is permitted, it's what kind of interpolation you should use. At a first glance it does not seem to me that linear interpolation is adequate, but, as I said, if you think its results are satisfactory then that's your decision to make.