2-way lookup that interpolates

[upchurchsophia]

Hi i'm an engineering student and I've got problems in interpolation in excel using 2-way lookup.
Let me show you an example:
[TABLE="width: 201"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]10
[/TD]
[TD]15
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD].25
[/TD]
[TD]-.3
[/TD]
[TD]-.5
[/TD]
[TD]-.6
[/TD]
[/TR]
[TR]
[TD]0.5
[/TD]
[TD]-.5
[/TD]
[TD]-.5
[/TD]
[TD]-.6
[/TD]
[/TR]
[TR]
[TD]1.0
[/TD]
[TD]-.7
[/TD]
[TD]-.6
[/TD]
[TD]-.6
[/TD]
[/TR]
</tbody>[/TABLE]

The red colored numbers (header) are roof angles.
the blue colored numbers (rows) are factors.
The black colored numbers are the coefficients.

i understand that 2-way lookup searches for the the number intersecting both angle and the factor.

if my factor is 0.5 and my roof angle is 15, the resulting coefficient is -0.5.
now my concern is, what if my factor is 0.6 and my roof angle is 18, (which by inspection requires two interpolations to happen)?

please help me. .thank you
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

Welcome to MrExcel.

What result would you expect for 0.6 and 18? And what would you expect to happen if one or both of the parameters were below or above those given?

 

[JB_Scotland]

As Andrew is eluding to the relationships in your table are non-linear so simple interpolation (which could easily be formulated) will not produce an accurate result.

It might help if further data was available so relationships could be more easily formulated. What is the source of your data and can you get data accurate to more precision than one decimal place?

 

[upchurchsophia]

thank you sir JB and sir Andrew, ,let me change my data, ,

[TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"][TABLE="width: 320"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl68, width: 192, colspan: 3"]Distance[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl65, align: right"]15[/TD]
[TD="class: xl65, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl67, align: right"]Slope ​2[/TD]
[TD="class: xl65, align: right"]0.378[/TD]
[TD="class: xl66, align: right"]0.416[/TD]
[TD="class: xl66, align: right"]0.457[/TD]
[TD="class: xl66, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl66, align: right"]0.680[/TD]
[TD="class: xl66, align: right"]0.748[/TD]
[TD="class: xl66, align: right"]0.823[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl66, align: right"]1.225[/TD]
[TD="class: xl66, align: right"]1.347[/TD]
[TD="class: xl66, align: right"]1.482[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]8[/TD]
[TD="class: xl66, align: right"]2.204[/TD]
[TD="class: xl66, align: right"]2.425[/TD]
[TD="class: xl66, align: right"]2.667[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl66, align: right"]3.968[/TD]
[TD="class: xl66, align: right"]4.365[/TD]
[TD="class: xl66, align: right"]4.801[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[TD="class: xl68, width: 192, colspan: 3"][/TD]
[/TR]
</tbody>[/TABLE]

Sir, if i have 5 meters and 4 degree slope, my coefficient is 0.680
my question sir is, what if my distance is 8 and my slope is 7 for example,
which is not given directly in the table, and is possible only through interpolation,
could you help me how to do it in excel?

 

[Andrew Poulsom]

Your data appears to be linear horizontally but not vertically. Is there an equation that determines it given a slope and distance?

 

[upchurchsophia]

ok, ,let's just assume sir it's linear. .just want to know how to interpolate such table in excel. .

 

[mirabeau]

ok, ,let's just assume sir it's linear. .just want to know how to interpolate such table in excel. .

This looks like an assignment rather than part of a serious project. Otherwise you'd be taking into account the obvious nonlinearity.

If you take into account all information contained in your table you'd be looking at an estimation (answer is 1.79, with 95% fiducial interval 1.49 to 2.09) rather than a linear interpolation which only takes account the nearest neighbor data points.

Answers to your question as posed are actually given in the thread

http://www.mrexcel.com/forum/excel-questions/690305-v-look-up-interpolate-simple-linear-interpolation-2.html

particularly from post#4 onward.

You may wish to look at that thread, to perhaps obviate the need for further replies here.