column and rows meet

  • Thread starter Thread starter Learcaptain
  • Start date Start date
L

Learcaptain

Aviation chart for takeoff field length.
I have 2 columns and 1 row.
1st column are elevations. Sea level to 14,000 ft in 1000's of ft.
(Sea level, 1000,2000, etc.)
2nd column are weights . 14,000 to 21,000 lbs in 1000's of lbs
(14,000, 15,000, etc)
first row across the top are temperatures. -40C to 50C in 10c
increments (-40, -30, etc).
I will have cells labeled Elevation, WT, and Temp which will be
manually inputed (keyboard). end result will be distance required for
takeoff.
Below is a fraction of the actual table.
ALT / FT WT / LB °C -40 -30
14000 2800 2900
15000 2840 2940
16000 2890 2990
Sea 17000 2940 3040
Level 18000 2990 3100
19000 3220 3340
20000 3550 3680
21000 3920 4050
21500 4190 4330
14000 2890 2990
15000 2930 3040
16000 2980 3090
17000 3030 3140
1000Ft 18000 3090 3200
19000 3320 3440
20000 3660 3790
21000 4030 4180
21500 4300 4470

Example. Elevation - 1000 ft
Weight - 20000 lbs
Temp - -30C

the end result will then be displayed in a cell
T/O distance - (3790)

I cant figure out how this will work , I thought maybe match , index,
anyway i'm lost.

Learcaptain
 
You presumably have a forrmula to do calculations or is this supposed to
be a lookup table ?
 
Hi,

Here we go. Put this in G4:

=VLOOKUP(G2,OFFSET(INDIRECT(ADDRESS((INT((MATCH(G1,A:A)-1)/9)-1)*9+2,MATCH(G3,C1:D1)+1)),,,9,3),2,FALSE)

Now the table is in A:D range (and it uses headers), so when you use
more of the temperature values than -40 and -30 you have to move this.

In G1 you have elevation, G2 weight and G3 temperature.

What you'll need to change is INT((MATCH(G1,A:A)-1)/9)-1)*9+2 that
part. If you have more than 9 weight values / altitute, you should
change the number nines.

Same goes for the last arguments of the ADDRESS-function (,9,3). 9
tells the amount of weights / altitute and 3 tells the amount of
temperature columns + 1.

Hope this helps.

- Asser
 
BrianB, no calculations required i am just trying a lookup.
Jazzer, I tried the formula on the example and it did not work.

The ALT column was shifted on the previous post.
lets if this one look better.
ALT / FT WT / LB °C -40 -30 Elevation 1000
14000 2800 2900 Weight 20000
15000 2840 2940 TEMP -30
16000 2890 2990 DISTANCE #N/A
17000 2940 3040
18000 2990 3100
19000 3220 3340
20000 3550 3680
Sea 21000 3920 4050
Level 21500 4190 4330
14000 2890 2990
15000 2930 3040
16000 2980 3090
17000 3030 3140
18000 3090 3200
19000 3320 3440
20000 3660 3790
21000 4030 4180
1000ft 21500 4300 447
 
Hi again,

let's see if I can help.

I assumed that in A column there is no empty cells. I mean, you shou
fill the altitude value for all the cell that they should apply.

In cells A2:A10 you should have 0. In A11:A19 1000 and so on...

- Asse
 
Thanks for the reply, Jazzer

Column A has sea level, 1000, 2000 , (the elevation)
Column B has weight of the aircraft, begining at 14,000-21,000 and the
21,500 max takeoff weight.
Row C2:P2 is temp -40 to 50 C.

I will try to attach file. So you can see it better

Attachment filename: lear45testformulas3.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=44657
 
Hi,

I made couble of changes. The major one is adding a new column a
Sheet3. For the lookup to work, there has to be the new column B.

I also deleted the C° part from Sheet3!D2.

Now in the Sheet1!C20 there is a messy formula that seems to work, bu
you should test it. I don't even try to explain how it works, but mayb
you can make something out of it :)

- Asse

Attachment filename: to.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=44755
 
Back
Top