Temperature vs altitude giving a weight limit

[flyting]

Hello experts

I am a helicopter pilot and we use tables like the one attached. As you can see the results are not uniform. On the left are altitude increments against temperatures and the bottom, giving us a limit on weight that we are allowed to fly with.

Where I need help is, I would like to somehow be able to put 2 of the 3 given above into an entry box and get a result of the 3rd from the figures in the table.
For example 7200 ft @ -3° = xxxxxxx


Is this at all possible?

Many Thanks....

[mikeTRON]

Sure you can.

Attached is the solution.
Height vs Temperature.xlsx

[mcmahobt]

Mike's solution doesn't seem to account for values between the ranges you have setup for altitude and temp. The attached workbook will let you enter any value (between the range limits) and will enter a value based on the closest overshoot for altitude, and undershoot for temperature (both done for safety).

[FDibbins]

I swapped your table around so the alt is in ascending order. try this...

(also, you have a ' on from of your 0 alt, you need to remove that)

[flyting]

Holy Smokes guys, that was quick.... AWESOME.... Thank you!

Can you do it for entering temp and weight to give an altitude result as well???

[mcmahobt]

See attached for inclusions.

[flyting]

Thanks mcmahobt.... have a nice day

[FDibbins]

Here is a slightly shorter version for your table...
=INDEX($A$1:$A$22,MATCH($T$3,OFFSET($A$1,0,MATCH($T$2,$B$23:$N$23),22,1)))

[flyting]

Something is wrong....???

It only goes to the nearest value and doesn't give me an exact number...

i.e. -10° and 2500kg - goes to 8500ft (true answer - 8288.8ft)

[mcmahobt]

Something is wrong....???

It only goes to the nearest value and doesn't give me an exact number...

i.e. -10° and 2500kg - goes to 8500ft (true answer - 8288.8ft)

Weird, on my end when I enter -20degC and 2500kg, the formula returns 8000, which is the first result for that combination based on the altitude Y-Axis you provided.

The formula will not return an exact calculation since none is present within the workbook you attached. If you explained the process of calculating altitude based on temperature and weight, a more accurate result could be produced.

[flyting]

Ok...
Thanks again for the help

[mcmahobt]

Were you able to recreate the results you posted earlier?

[flyting]

The problem is I need the in-between heights to be calculated when I give a weight and temp that is not in the numbers on the table. As it is now, I have to work out the altitudes manually.

I was hoping it could be done with excel...

[Pepe Le Mokko]

To all : be aware that if any help you provide concerning calculated altitude and weight , or any other flight data leads to an accident, your responsibility might be engaged. !

Just as an example : OP does not say which altitude settings are used : QNH, QFE, regional QNH ? What about FL?

[flyting]

No Pepe
Firstly, I'm not in the US and secondly, it is used as a guide. Not an exact fixed complient! And QNH Etc. has nothing to do with it...
So helpers don't worry..... We won't be "coming after you"

[MrShorty]

When I see these kind of questions, I often wonder how much of the question is really about Excel/spreadsheet programming and how much of it is about the science/engineering/math behind the question. The question has been framed as a spreadsheet programming question, but I find that I must first understand the science/engineering/math behind the question before I can tackle the programming part of the question.

Assuming that this question is really more about aeronautics than about spreadsheet programming, I ran a couple of quick internet searches and came across concepts like "density altitude" and "weight altitude temperature limit" that seem to be related to this discussion. (For example: http://www.langleyflyingschool.com/P...0Altitude.html
http://en.wikipedia.org/wiki/Density_altitude
http://onlinelibrary.wiley.com/doi/1...7.ch26/summary (I think you will need to pay to access this one online, or find the text in a library).

If you will help us understand the science/math behind the question and what formulas you want/need to use for this calculation, we should be able to help you program those calculations in Excel.

[mcmahobt]

For your altitude calculations then, is it an interpolation calculation? Or is there a specific formula you use to output altitude based on temperature and weight? What I'm getting at is, not everyone here is knowledgeable in aviation, and as such would need more information on how you specifically calculate altitude.

[flyting]

Yup.... basic interpolation... I just want a quick easy way to do it, especially when the temp & weight are between the numbers given

[flyting]

Stop thinking too much into my question....
I am sitting at my base and need to know roughly how high I can take my helicopter on a given day with those "predicted" temperatures with my calculated aircraft weight (when loaded).
It isn't an exact because when we get there the temperature might be different and there might be a wind which alters everything.

All I'm asking is if the original excel sheet can be manipulated in such a way, that when I give it a specific weight and a specific temperature, it will give a good average of the calculated altitude using an interpolation calculation.
If I have to do it myself old school style it takes a few minutes, where as excel can crunch it in a few seconds.....

Just trying to make life a little easier...!

[mcmahobt]

Not a pretty formula by any means, but it returns 8,286 ft based on your previous conditions. Couldn't get the forecast function to work correctly (off by ~200 ft from your previous example), but this bear of a formula still seems to work. Paste into T4 of my most recent workbook. Hopefully someone more skilled at data manipulation can condense it:

Please Login or Register  to view this content.

[flyting]

That's a book you just wrote for me mc....

Works great for the temperatures given and in between weights: 10° @ 2700kg = 4769ft........... which is on the money
but try in between temps: 13° @ any weight and it throws an error...

[mcmahobt]

There are no weights that correspond to 13degC?

[FDibbins]

If this is a guide, and you want exact weights, sounds to me like you would need a larger table with exact weights - or else just use it as a guide, with the suggested ranges?

[mcmahobt]

Agreed with Ford.

[flyting]

it has to be calculated - same as when I have a weight between 2 given weights on the table.
For example, we will often have 13° @ 2900kg, which is an interpolation of an interpolation... which will take even longer

[FDibbins]

I think you will be better off expanding your table to cater for all those "inside" values. That way, the lookups will be far simpler and more accurate for your "guide"

[shg]

Row\Col	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Alt \ Temp	(20)	(15)	(10)	(5)	0	5	10	15	20	25	30	35	40
3	10,000	2,340	2,345	2,345	2,290
4	9,000	2,435	2,435	2,435	2,404	2,363	2,295
5	8,000	2,526	2,526	2,526	2,495	2,463	2,417	2,367	2,313
6	7,000	2,622	2,626	2,626	2,599	2,567	2,517	2,467	2,422	2,376	2,313
7	6,000	2,722	2,726	2,730	2,694	2,667	2,617	2,572	2,522	2,476	2,408	2,308
8	5,000	2,821	2,826	2,830	2,803	2,776	2,726	2,676	2,621	2,572	2,513	2,417	2,313
9	4,000	2,948	2,939	2,921	2,903	2,885	2,830	2,780	2,730	2,680	2,622	2,522	2,417	2,313
10	3,000	2,948	2,948	2,948	2,948	2,930	2,912	2,894	2,844	2,785	2,730	2,631	2,517	2,413
11	2,000	2,948	2,948	2,948	2,948	2,948	2,948	2,939	2,916	2,889	2,830	2,744	2,640	2,522
12	1,000	2,948	2,948	2,948	2,948	2,948	2,948	2,948	2,948	2,948	2,916	2,858	2,744	2,640
13	0	2,948	2,948	2,948	2,948	2,948	2,948	2,948	2,948	2,948	2,948	2,948	2,871	2,740
14
15												Alt	Temp	Load
16												7,500	13	2,387
17												4,400	27	2,539
18												10,000	(4)	#VALUE!	<< no data
19

It uses some user-defined functions, so you'll need to enable macros.

EDIT: BTW - I deleted the interpolated rows that were embedded iin your table. They don't add anything.

[bebo021999]

Give a shot into Alt calculation with given temp and Weight.

Not sure it's correct or not, but my formula returns:

Temp (Cell S1): 13
Weight (Cell S2) : 2500
Alt: 6412

My solution is:

1) Calculate rank of percentage of 13 (Cell S1) in dataset {10,15}:
=1-PERCENTRANK(OFFSET($A$23:$B$23,,MATCH($S$1,$B$23:$N$23),),$S$1)
(The green part refer to H23:I23 range)
=0.4

2) Create a helper column O to return the "0.4 - th" value between the two value in H1:I1:
In O1 then drag down:
=PERCENTILE(H1:I1,0.4)
=>
=PERCENTILE(OFFSET($A1:$B1,,MATCH($S$1,$B$23:$N$23),),1-PERCENTRANK(OFFSET($A$23:$B$23,,MATCH($S$1,$B$23:$N$23),),$S$1))

the results for instant:
10 Degree: H5 =2367
15 degree: I5=2313
13 degree: O5=2334.6

Now for W 2500, we can see O8:O9 with 2491 and 2542, step 3

3) Calculate rank of percentage of 2500 (Cell S2) in dataset {2491,2542}:
=1-PERCENTRANK(OFFSET($O$1:$O$2,MATCH($S$2,$O$1:$O$22)-1,),$S$2)
(The green part refer to O8:O9 range)
=0.824

4) To return the "0.824 - th" value between the two value in A8:A9
In S3:
=PERCENTILE(A8:A9,0.824)
=>
=PERCENTILE(OFFSET($A$1:$A$2,MATCH($S$2,$O$1:$O$22)-1,),1-PERCENTRANK(OFFSET($O$1:$O$2,MATCH($S$2,$O$1:$O$22)-1,),$S$2))
=6412 ft

To check the result manually:
* Position of 13 btw 10 and 15 = (13-10)/5=0.6
but the weigh is decending = 1-0.6=0.4

*Apply for I8 and I9:
=(H8-I8)*0.4+I8 = (2520-2472)*0.4-2472=2491
=(H9-I9)*0.4+I9 = (2572-2522)*0.4-2522=2542

* Position of 2500 btw 2491 and 2542 = (2500-2491)/(2542-2491)=0.176
but the height is decending = 1-0.176 = 0.824

*Apply that rate for the height A8:A9 (6000:6500)
=(A8-A9)*0.824+A9
=(6500-6000)*0.824 + 6000
=6412 ft

Does it correct?

See attachment.

[flyting]

Thank you everyone for the help....
This is definitely one site that will be added to my fav's