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

[samtheman88]

Hi,

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

Stage (H)	Outflow (O)	Storage (S)	2S/Δt	2S/Δt + O
feet	cfs	acre-ft	cfs	cfs
0	0.00	0	0	0.00
0.1	24.44	0.4	9.68	34.12
0.2	34.57	0.8	19.36	53.93
0.3	42.34	1.2	29.04	71.38
0.4	48.89	1.6	38.72	87.61
0.5	54.66	2	48.4	103.06
0.6	59.88	2.4	58.08	117.96
0.7	64.67	2.8	67.76	132.43
0.8	69.14	3.2	77.44	146.58
0.9	73.33	3.6	87.12	160.45
1	77.30	4	96.8	174.10

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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

Inflow (I0) 2S/Δt - O 2S/Δt + O Outflow (O) Stage (H) cfs cfs cfs cfs feet 0 0 0 0.00 50 50 0.00 100 <colgroup><col span="3"><col><col></colgroup><tbody> </tbody>

<colgroup><col span="5"><col></colgroup><tbody>
</tbody>

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.

 

[mikerickson]

Try this link
http://www.mrexcel.com/forum/excel-...n-value-when-falls-between-range-numbers.html

 

[samtheman88]

Try this link
http://www.mrexcel.com/forum/excel-...n-value-when-falls-between-range-numbers.html

That seems like it would work, but might add a lot of error because I'm dealing with many lines of data (more than I posted). I'm going to try the following, method and see how it works.

=FORECAST(NewX,OFFSET(KnownY,MATCH(NewX,KnownX,1)-1,0,2),
OFFSET(KnownX,MATCH(NewX,KnownX,1)-1,0,2))

 

[richthorpe]

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

Height</SPAN>	distance to shoreline (km)</SPAN>
</SPAN>	0.1	2	10	100
2</SPAN>	1.90</SPAN>	1.60</SPAN>	1.50</SPAN>	1.40
3</SPAN>	2.15</SPAN>	1.84</SPAN>	1.73</SPAN>	1.62
4</SPAN>	2.31</SPAN>	2.03</SPAN>	1.90</SPAN>	1.78
5</SPAN>	2.43</SPAN>	2.18</SPAN>	2.05</SPAN>	1.90
10</SPAN>	2.82</SPAN>	2.65</SPAN>	2.50</SPAN>	2.32
15</SPAN>	3.07</SPAN>	3.02</SPAN>	2.85</SPAN>	2.67
20</SPAN>	3.20</SPAN>	3.15</SPAN>	2.98</SPAN>	2.78
30</SPAN>	3.42</SPAN>	3.43</SPAN>	3.27</SPAN>	3.04
50</SPAN>	3.68</SPAN>	3.68</SPAN>	3.62</SPAN>	3.39
100</SPAN>	3.98</SPAN>	3.98</SPAN>	3.98</SPAN>	3.80

<TBODY>
</TBODY>

 

[mikerickson]

Are the heights and distances always in ascending order?

 

[shg]

One way, using a UDF:

       --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

 

[shg]

Backwards, sorry:

       ----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

 

[richthorpe]

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

Backwards, sorry:

       ----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

 

[richthorpe]

Yes the factors get larger with increased height. Similarly the values decrease with distance from the shoreline

 

[mirabeau]

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

	A	B	C
1		10	100
2	5	2.05	1.9
3	10	2.5	2.32

<tbody>
</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.

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