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

S

samtheman88

New Member
Joined
Feb 6, 2013
Messages
7
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Upvote 0
Hi everyone,

I Have a problem that would like some help with. I have a range of data that I need to interpolate between to give me a specific factor that needs using in another formula. I need to interpolate betwee a range of heights and also a range of distances from the sea in order to give me the value I need. Linea interpolation between values is fine. This is the data I have. Note the values in bold is the data range. In my example I want to return a value for a height of 7m and distance to shoreline of 20km

[TABLE="width: 320"]
<TBODY>[TR]
[TD]Height</SPAN>
[/TD]
[TD="colspan: 3"]distance to shoreline (km)</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]</SPAN>
[/TD]
[TD="align: right"]0.1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>
[/TD]
[TD="align: right"]1.90</SPAN>
[/TD]
[TD="align: right"]1.60</SPAN>
[/TD]
[TD="align: right"]1.50</SPAN>
[/TD]
[TD="align: right"]1.40[/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>
[/TD]
[TD="align: right"]2.15</SPAN>
[/TD]
[TD="align: right"]1.84</SPAN>
[/TD]
[TD="align: right"]1.73</SPAN>
[/TD]
[TD="align: right"]1.62[/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>
[/TD]
[TD="align: right"]2.31</SPAN>
[/TD]
[TD="align: right"]2.03</SPAN>
[/TD]
[TD="align: right"]1.90</SPAN>
[/TD]
[TD="align: right"]1.78[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]2.43</SPAN>
[/TD]
[TD="align: right"]2.18</SPAN>
[/TD]
[TD="align: right"]2.05</SPAN>
[/TD]
[TD="align: right"]1.90[/TD]
[/TR]
[TR]
[TD="align: right"]10</SPAN>
[/TD]
[TD="align: right"]2.82</SPAN>
[/TD]
[TD="align: right"]2.65</SPAN>
[/TD]
[TD="align: right"]2.50</SPAN>
[/TD]
[TD="align: right"]2.32[/TD]
[/TR]
[TR]
[TD="align: right"]15</SPAN>
[/TD]
[TD="align: right"]3.07</SPAN>
[/TD]
[TD="align: right"]3.02</SPAN>
[/TD]
[TD="align: right"]2.85</SPAN>
[/TD]
[TD="align: right"]2.67[/TD]
[/TR]
[TR]
[TD="align: right"]20</SPAN>
[/TD]
[TD="align: right"]3.20</SPAN>
[/TD]
[TD="align: right"]3.15</SPAN>
[/TD]
[TD="align: right"]2.98</SPAN>
[/TD]
[TD="align: right"]2.78[/TD]
[/TR]
[TR]
[TD="align: right"]30</SPAN>
[/TD]
[TD="align: right"]3.42</SPAN>
[/TD]
[TD="align: right"]3.43</SPAN>
[/TD]
[TD="align: right"]3.27</SPAN>
[/TD]
[TD="align: right"]3.04[/TD]
[/TR]
[TR]
[TD="align: right"]50</SPAN>
[/TD]
[TD="align: right"]3.68</SPAN>
[/TD]
[TD="align: right"]3.68</SPAN>
[/TD]
[TD="align: right"]3.62</SPAN>
[/TD]
[TD="align: right"]3.39[/TD]
[/TR]
[TR]
[TD="align: right"]100</SPAN>
[/TD]
[TD="align: right"]3.98</SPAN>
[/TD]
[TD="align: right"]3.98</SPAN>
[/TD]
[TD="align: right"]3.98</SPAN>
[/TD]
[TD="align: right"]3.80[/TD]
[/TR]
</TBODY>[/TABLE]
 
Last edited:
Upvote 0
One way, using a UDF:

Code: 
       --A-- --B--- --C--- --D--- --E--- F G H- -I-- -------------J--------------
   1   x \ y   0.1      2     10    100    x y   z                               
   2       2  1.90   1.60   1.50   1.40    7 20 3.04 I2: =BiLInterp(G2,H2,A1:E11)
   3       3  2.15   1.84   1.73   1.62                                          
   4       4  2.31   2.03   1.90   1.78                                          
   5       5  2.43   2.18   2.05   1.90                                          
   6      10  2.82   2.65   2.50   2.32                                          
   7      15  3.07   3.02   2.85   2.67                                          
   8      20  3.20   3.15   2.98   2.78                                          
   9      30  3.42   3.43   3.27   3.04                                          
  10      50  3.68   3.68   3.62   3.39                                          
  11     100  3.98   3.98   3.98   3.80

There is a .bas module with the code at https://www.box.com/s/s0m5gs96jd3stuqa7tpu
 
Upvote 0
Backwards, sorry:

Code: 
       ----A----- --B--- --C--- --D--- --E--- F -G- -H-- -I-- -------------J--------------
   1   hgt \ dist   0.1      2     10    100    hgt dist data                             
   2           2   1.90   1.60   1.50   1.40      7   20 2.21 I2: =BiLInterp(H2,G2,A1:E11)
   3           3   2.15   1.84   1.73   1.62                                              
   4           4   2.31   2.03   1.90   1.78                                              
   5           5   2.43   2.18   2.05   1.90                                              
   6          10   2.82   2.65   2.50   2.32                                              
   7          15   3.07   3.02   2.85   2.67                                              
   8          20   3.20   3.15   2.98   2.78                                              
   9          30   3.42   3.43   3.27   3.04                                              
  10          50   3.68   3.68   3.62   3.39                                              
  11         100   3.98   3.98   3.98   3.80
 
Upvote 0
Thanks shg, that looks as though it would work well. Unfortunately the version of excel I have on my laptop is 2003 (tight company won't upgrade!). Is there another formula that will work with 2003? Unfortunately I couldn't access the box.com website as my company has blocked access! Any further help would be greatly recieved
shg said:
Backwards, sorry:

Code: 
       ----A----- --B--- --C--- --D--- --E--- F -G- -H-- -I-- -------------J--------------
   1   hgt \ dist   0.1      2     10    100    hgt dist data                             
   2           2   1.90   1.60   1.50   1.40      7   20 2.21 I2: =BiLInterp(H2,G2,A1:E11)
   3           3   2.15   1.84   1.73   1.62                                              
   4           4   2.31   2.03   1.90   1.78                                              
   5           5   2.43   2.18   2.05   1.90                                              
   6          10   2.82   2.65   2.50   2.32                                              
   7          15   3.07   3.02   2.85   2.67                                              
   8          20   3.20   3.15   2.98   2.78                                              
   9          30   3.42   3.43   3.27   3.04                                              
  10          50   3.68   3.68   3.62   3.39                                              
  11         100   3.98   3.98   3.98   3.80
Click to expand...
 
Upvote 0
Yes the factors get larger with increased height. Similarly the values decrease with distance from the shoreline
 
Upvote 0
richthorpe,

this post is unlikely to be very useful for your purposes, but you may get some interest from it.

You want to obtain the value (by linear interpolation) for height of 7 and distance of 20.

This would be by interpolation in the following part of your table
Sheet1


<tbody>
[TD="align: right"]A[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]C[/TD]

[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"] 10 [/TD]
[TD="align: right"] 100 [/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"] 5 [/TD]
[TD="align: right"]2.05[/TD]
[TD="align: right"]1.9[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"] 10 [/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]2.32[/TD]

</tbody>

Put this in the range A1:C3 as indicated, in a blank worksheet.
Run the following macro and it should give all linear interpolations from this table, including marking the one you ask about.
Code: 
Sub interp()
Dim a, x&, y&, j
Dim c As Range, d As Range

a = Cells(1).CurrentRegion
x = 0.1 * (a(1, 3) - a(1, 2))
y = a(3, 1) - a(2, 1)

Rows(3).Resize(y - 1).Insert
Set c = Cells(1)
Do
Set d = c.End(4)
If c(2) = vbNullString Then
    Do
    For j = 0 To y - 3
        c.Offset(1, j) = _
            c.Offset(, j) + (d.Offset(, j) - c.Offset(, j)) / (d.Row - c.Row)
    Next j
    Set c = c.Offset(1)
    Loop Until c = d(0)
End If
Set c = d
Loop Until d.End(4).Row = Rows.Count

Columns(3).Resize(, x - 1).Insert
Set c = Cells(2)
Do
Set d = c.End(2)
If c.Offset(, 1) = vbNullString Then
    Do
    For j = 0 To x - 3
        c.Offset(j, 1) = _
            c.Offset(j) + (d.Offset(j) - c.Offset(j)) / (d.Column - c.Column)
    Next j
    Set c = c.Offset(, 1)
    Loop Until c = d.Offset(, -1)
End If
Set c = d
Loop Until d.End(2).Column = Columns.Count

With Application
    x = Application.Match(20, Rows(1), 1)
    y = Application.Match(7, Columns(1), 1)
    Cells(y, x).Interior.Color = vbCyan
End With
End Sub
Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA linear interpolation FX forward points
Replies
0
Views
3K
macfoolish
M
B
  • Question Question
increment by input message box and interpolate between removed data
Replies
5
Views
529
Brandon M
B
J
  • Question Question
Select value from table looking up two different ranges
Replies
4
Views
574
Aladin Akyurek
Aladin Akyurek
D
  • Question Question
Look Up with Sum
Replies
2
Views
379
oldbrewer
O
P
  • Question Question
Linear Interpolation Function - Using for non-linear data
2
Replies
12
Views
11K
DiGiMac
DiGiMac

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top