Interpolation from table

c_t

New Member
Joined
Aug 8, 2002
Messages
4
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
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

New Member
Joined
Aug 8, 2002
Messages
4
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

New Member
Joined
Jun 26, 2002
Messages
42
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)
 

c_t

New Member
Joined
Aug 8, 2002
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,747
Messages
5,638,118
Members
417,010
Latest member
jnuss03

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
Top