Interpolation from table

[c_t]

Help!
I have being trying to get this to work but with no success. I want to input a value for He and a value for distance to the sea and have excel interpolate a value from the table. Any suggestions please?


Site in Country
He Distance to Sea km
0 2 10 100<2 1.48 1.4 1.35 1.26
5 1.65 1.62 1.57 1.45
10 1.78 1.78 1.73 1.62
15 1.85 1.85 1.82 1.71
20 1.9 1.9 1.89 1.77
30 1.96 1.96 1.96 1.85
50 2.04 2.04 2.04 1.95
100 2.12 2.12 2.12 2.07
This message was edited by c_t on 2002-09-06 03:06

 

[ChrisUK]

You need to be clearer what it is you want, your table doesn't make any sence to me I'm affraid

If you want to go to a specific column dependant on if an answer is less then 2 or between 5 and 10 etc you can use vlookup

Eg:

Cell A10 has 7
Cell A11 has 2

Formula is : VLOOKUP(A10,mytable,A11,TRUE)

mytable is the table of data you had starting in cell A1 (doesn't matter where you start really so long as you name the range)

This formula would give 1.62 from your example data

Hope this helps

 

[c_t]

sorry, i just reread my original post and its not very clear

........A........
B | 5 | 10 | 15
1 || 1 | 5 | 23
2 || 2 | 6 | 31
3 || 4 | 9 | 50

Using the table above

If the A value was 10 and the B value 2, the result would be 6.
I need to interpolate between the values, ie A = 11 and B = 1.4 to find the resultant. I will assume linear change between each value.

i hope this clarifies
This message was edited by c_t on 2002-09-06 04:33
This message was edited by c_t on 2002-09-06 04:35

 

[gvanbee]

If I set your data up in an array contained in A1:D4,
A will be put into A9, B will be put into B9
then put the following equations in:

B6=TREND(B2:B4,$A2:$A4,$B9)
C6==TREND(C2:C4,$A2:$A4,$B9)
D6=TREND(D2:D4,$A2:$A4,$B9)

Answer is:
D9=TREND(B6:D6,B1:D1,A9)

 

[Brian from Maui]

Try this thread, I had a similar problem

http://www.mrexcel.com/board/viewtopic.php?topic=19160&forum=2

 

[c_t]

i used the trend function and it did worked ok

unfortunately, its not quite what i need

if i plot x^2 on an xy graph the values will be 1,4,9,16,25 etc.
this is obviously a curve.

trend will draw a best fit straight line through this curve. this means if i enter a figure of 3 i expect the result to be 9.

trend does not give me this result.

whilst using an x^2 curve is exaggerating the problem, it illustrates well why i cant use it.

i need to read directly from the table when the value is exact, and interpolate between the 2 adjacent values when it is not exact.

would LINEST be of any use?
This message was edited by c_t on 2002-09-09 06:13