automatic calculation of mean wind in excel

[lusthansa]

Hi there,

I am just about to write my own comprehensive flight planning tool in MS 2003 Excel. Beside other things it shall feature also the automatic determination of the wind direction (and later on speed, too) in a flight altitude / flight level for which you do not have a direct information on W/V.

E.g.:

Wind + Temp Chart 50 FL says (at your place): W/V 300/05
Wind + Temp Chart 100 FL says (at your place): W/V 010/10

lets assume wind turns linearly with altitude and windspeed changes linearly, too with altitude. Then you had here when flying in 7500 FT (ISA):

W/V: 335/7.5

But if you tell Excel to make weird calculations with cosinus / sinus, etc. you will always have a problem with the wind / wind speed not rising / falling linearly with ALT.

If you take just a mean formula as (W/V 1 + W/V 2) / 2 you have a prob already when one wind is located beyond the 360° - "border" with respect 2 the other wind. Here we had then: (300 + 10) / 2 = 310/2 = 155, that is not equal to the above mentioned result 335/ ... but a 180° - error which is not acceptable for planning. I did not succeed in finding a rule that is applicable 4 all alts telling excel when to add 180° / when to add 0° / when to add 360° due to quadrantal error.

Who can help ? I have been torturing my head for 10 hrs now it´s damn difficult ...

[Low life]

You could always use vlookup

[LH2]

The problem is that wind could veer as well as back so it is important that your algorithm takes into account the sense (direction) of the operations.


For example, let:
. w0 be your initial wind direction
. v0 be your initial wind speed
. h0 be your initial altitude
. w1 be your final wind direction
. v1 be your final wind speed
. h1 be your final altitude

The important bit here is that it's not the same going from x0 to x1 than going from x1 to x0.

In that case, for an altitude hn your W/V is:

vn = (v1-v0) / (h1-h0) * (hn-h0) + v0
w0 = w0 % 360 [ where % denotes the modulo operation ]
w1 = w1 % 360 [ so that 0 >= w > 360 ]
w1' = (w1 <= w0 ? w1 : (w1 + 360)) [ make sure w1' <= w0 ]
wn = ((w1'-w0) / (h1-h0) * (hn-h0) + w0) % 360



I'm pretty sure Excel provides all the features needed to implement the above algorithm. I'm sorry I can't provide the specific syntax, but I don't use Excel.
Hope this helps,
LH2

[atcfalcon]

Hi Lusthansa,

check your personal messages !

I did the windproblem-thing when I worked on the whole file. I don´t know how much you are into Excel - by clicking on one of the fields with the numbers in it you can see the actual calculation formula in the top line below the symbolline.
I deleted the rest of the formulas which could be found beside the windcalculation though . :cool:

brgds, atcfalcon

[Old Smokey]

This is an old number crunching problem. Think about it, the Absolute difference between any two compass directions can never exceed 180° (-180° to +180°). This can be solved with a one-liner with two operands,

-Diff *(abs(diff)<=180)-(360+(abs(diff)>180))

(Where "Yes" from the logical operand yields -1, and "No" yields 0)

Done in great haste, check the signs, but there's a start point.

Regards,

Old Smokey