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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,486
Messages
5,837,611
Members
430,505
Latest member
DevAlex

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