Finding intersection of row and column (both variable) in table

[Guest]

I'm building a spreadsheet to use for my own flight planning purposes. One
thing I have to calculate is landing distance which is based on two
variables, temperature and altitude. Below is a table that comes from the
plane manual: first column is airport elevation in thousands of feet, the
other three columns are landing distances in feet for 10, 20, and 30 degrees
Celsius.

Alt. 10 C 20 C 30 C
000 695 755 810
1000 765 825 890
2000 840 910 980
3000 925 1000 1080
4000 1020 1100 1190
5000 1125 1215 1315
6000 1245 1345 1455
7000 1375 1490 1615
8000 1525 1655 1795

Basic Question: How in Excel, would I enter an expression that takes the
altitude and current temperature input by me and look up the corresponding
landing distance? In looking through the posts, it looks like an Index,
Match, Match scenario, but I can't get it to work correctly.

Advanced Question: How could I get Excel to interpolate data based on
variables that aren't strictly listed in the table. For example, the airport
elevation is 1134 feet with a temperature of 13 degrees?

Any help with this would be greatly appreciated.

Alan

 

[Jason Morin]

One way:

=INDEX(A110,MATCH(F1,A:A,0),MATCH(F2,1:1,0))

where F1 holds the alt. and F2 holds the temp.

HTH
Jason
Atlanta, GA

 

[Biff]

Hi!

Assume that you enter your variables in cells A1 and B1
for Alt and Temp, respectively.

The table is in the range A514.
A55 are the Alt and Temp headers.

For ease of use it would be best to drop the "C" in the
Temp headers and will therefor not be necessary to enter
it in cell B1 as part of the match string.

Enter this formula in cell C1:

=IF(ISERROR(VLOOKUP(A1,A614,MATCH(B1,B55,1)
+1,1)),"",VLOOKUP(A1,A614,MATCH(B1,B55,1)+1,1))

You can set the RANGE LOOKUP and MATCH TYPE arguments to
return a "closest" match. In this example both arguments
are set to 1. See Excel help on these functions for a
detailed explanation on those arguments.

Biff

 

[Guest]

Another option to play with, which includes the interpolation part ..

Assume the table as posted is in A110
and the values in B11 are simply: 10, 20, 30 (i.e. w/o the "C")

Let's earmark:
A12 for the Alt input,
B12 for the temp input
C12 will be the output

Put in E1: =B12

Put in E2: =FORECAST($E$1,B22,$B$1:$D$1)
Copy E2 down to E10

Put in A14:

=OFFSET($A$1,MATCH($A$12,$A$1:$A$10,TRUE)+ROWS($A$1:A1)-2,COLUMNS($A$1:A1)*4-4)

Copy A14 across to B14, fill down to B15

Now put in C12:

=IF(ISERROR(INDEX($A$1:$D$10,MATCH(A12,$A$1:$A$10,0),MATCH(B12,$1:$1,0))),(A12-A14)/(A15-A14)*(B15-B14)+B14,INDEX($A$1:$D$10,MATCH(A12,$A$1:$A$10,0),MATCH(B12,$1:$1,0)))

(The INDEX formula part within C12 is essentially the same as what Jason
posted earlier)

C12 will output the exact matched value in the table if the Alt and temp
inputs match, with non-matching inputs calculated.

 

[Alan Beban]

I'm building a spreadsheet to use for my own flight planning purposes. One
thing I have to calculate is landing distance which is based on two
variables, temperature and altitude. Below is a table that comes from the
plane manual: first column is airport elevation in thousands of feet, the
other three columns are landing distances in feet for 10, 20, and 30 degrees
Celsius.

Alt. 10 C 20 C 30 C
000 695 755 810
1000 765 825 890
2000 840 910 980
3000 925 1000 1080
4000 1020 1100 1190
5000 1125 1215 1315
6000 1245 1345 1455
7000 1375 1490 1615
8000 1525 1655 1795

Basic Question: How in Excel, would I enter an expression that takes the
altitude and current temperature input by me and look up the corresponding
landing distance? In looking through the posts, it looks like an Index,
Match, Match scenario, but I can't get it to work correctly.

I would change the column headings to 10C, 20C and 30C (without the
spaces) and the altitudes to 000ft, 1000ft, 2000ft etc. Then highlight
the range and click on Insert|Name|Create and check Top row and Left
column. Then you can use, e.g., =_20C _5000ft to return 1215

Alan Beban

 

[Guest]

Thanks all for the quick replies...am trying to work all your examples with
the complete table. Will let you know of the results.

 

[Guest]

Max,

Again, thanks for the quick help. I'm playing around with your idea, but it
doesn't seem to be working quite right. Unfortunately, I'm an idiot when
using your formulas.

The problem seems to be, as altitude and temperature increase, the values
being generated should rise as well. In using your example, with the values
of 1100 for altitude and 10 for temperature, the output value is actually
lower than the value for 1000 and 10.

Any ideas?

Happy New Year,
Alan

 

[Max]

The problem seems to be, as altitude and

temperature increase, the values being
generated should rise as well.

In using your example, with the values
of 1100 for altitude and 10 for temperature,
the output value is actually
lower than the value for 1000 and 10.

Strange, as this is what I got as the outputs:

Alt-Temp-Result
1000-10-765 (exact value from table)
1100-10-772 (interpolated/calc)

i.e. with 772 higher than 765 ?

If you like, I could send you a sample book
with the suggested set-up implemented.
Just post a "readable" email in response here

 

[Max]

If desired, we could also set-up a 2 variable data table
in the same sheet ..

Experiment with this extension to the set-up ..

Put in say, A19: =C12
(just a link to the output cell of interest)

List some possible inputs for Temp
horizontally into B19:G19
say, the values: 10, 11, 12, ... 15
(for the row input cell)

and some possible inputs for Alt
vertically down in A20:A30
say: 1000, 1100, .. 2000
(these will be for the column input cell)

Select A19:G30

Click Data > Table
For "Row input cell", put: B12
For "Column input cell", put: A12
Click OK

(Mask A19 by formatting the font as white)

The above will drive out the table below in A19:G30:

974 10 11 12 13 14 15
1000 765 770 777 783 789 795
1100 772 778 784 791 797 803
1200 779 786 792 799 805 811
1300 787 793 800 806 813 819
1400 795 801 808 814 821 827
1500 802 809 815 822 829 835
1600 810 816 823 830 836 843
1700 817 824 831 838 844 851
1800 825 832 839 845 852 859
1900 832 839 846 853 860 867
2000 840 847 854 861 868 875

You could also simply overwrite
the Temp and/or Alt variable values
listed in either B19:G19 and A20:A30
with other possible input values, and
the intersecting values within (B20:G30)
will be recalculated accordingly