Wind Calculator

[aviator]

Hi there, this is my forst time to the board. Hope you can help. I am a pilot and I am trying to write a wind calculator program for the Flight Planning Dept of my Company. This is the situation. Environment Canada issues winds (velocity and drection)for certain altitudes i.e. 12000 feet ASl 18000 feet ASL etc. This data is taken from hundreds of stations across Canada. They do not list all the altitudes though. My program will list the velocity and speed at the altitudes not given by Environment Canada. The math is very basic but the progam will save the dispatchers al lot of time. Here is my problem. Wind is given is degrees thus South equals 180, West 270 and North 360 etc. I was using this formula "=MOD(AVERAGE(B5,E5),360)" however wind always increases clockwise as the altitude increases. So if I have a wind at 12000 feet of 340 degrees and 010 degrees at 18000 the program will give an average of 175 which is mathematically correct but not realistic (it would be 355. How can I get the program to always add the average to the first variable...in this case the 340?

 

[eliW]

Hi Aviator and welcome to the board.

Look at the following example and please tell if that what you need

Eli

Book1
	A	B	C	D	E	F	G	H	I
1
2
3
4		12000			18000		AVERAGE
5		340			10		355
6		10			340		175
7
8
9
Sheet1

 

[Whisperer14]

Hi Aviator,

Welcome to the board. From the information that you have given Eli's response is perfect. Unfortunately the wind does not always foolow what it is supposed to do - veer and increase with height - and very occasionally it will back. In this case Eli's solution will display the reciprocal to what you want.

The following code is unwieldy but does cover both eventualities, the normal and the occasional abnormal behaviour of the Wind direction. Eli please feel free to amend to a less unwieldy format but the result of 240 must be available with a lower wind of 250 and a higher wind of 230.

=IF(OR(B1-A1<-180,B1-A1>=0),IF(A1>B1,(360-A1+B1)/2,(B1-A1)/2),(B1-A1)/2)+A1

HTH

Retired aviator

 

[eliW]

Hi Aviator,

Welcome to the board. From the information that you have given Eli's response is perfect. Unfortunately the wind does not always foolow what it is supposed to do - veer and increase with height - and very occasionally it will back. In this case Eli's solution will display the reciprocal to what you want.

The following code is unwieldy but does cover both eventualities, the normal and the occasional abnormal behaviour of the Wind direction. Eli please feel free to amend to a less unwieldy format but the result of 240 must be available with a lower wind of 250 and a higher wind of 230.

=IF(OR(B1-A1<-180,B1-A1>=0),IF(A1>B1,(360-A1+B1)/2,(B1-A1)/2),(B1-A1)/2)+A1

HTH

Retired aviator

I have no idea in aviation but I except the challenge!

The formula according to Whisperer14 correction will be as follows:

Eli

Book3
	C	D	E	F	G	H	I
1
2
3		L	H		RESULT
4		250	230		240
5		230	250		240
6		10	340		175
7		340	10		355
8
9
10
11
Sheet1

 

[Whisperer14]

Eli

Solution perfect, thanks for the modification.

 

[aviator]

Wow!!! you guys are amazing, I will adjust my program and get back to you. I will have another question soon.

 

[aviator]

Next problem. My calculator right now is set up so that I can enter 2 sets of winds from 2 stations. I can insert wind from point A at 2 seperate altitudes,(lets say again 12000 and 18000) and winds from point B at 2 seperate alititudes, and the program will give me the average of all the winds combined at each altitudes 12,13,14 etc.

I would like to be able to add a sheet in my program where I can enter 3 sets of winds (maybe 4 but not yet, lets work on three for now. (heh heh)

Someone wrote me the following code, I do not really understand them and I want to know how I can alter these codes for 3 stations. These codes are at the bottom of my program.

The first one is in A 26 and is called "flight level" =$B$3

underneath A 27 is =A26+10, each subsequent altitude has the A increasing by 1 digit.

The second part is Wind direction in B26 through 39
=MOD($G$8+($G$15-$G$8)*((A26-$B$3)/($B$10-$B$3)),360)

Finally velocity C 26 to 39
=$G$6+($G$13-$G$6)*(($A26-$B$3)/($B$10-$B$3))

Anything else you need? Can I post the program here?

 

[PaddyD]

"Can I post the program here"

Download colo's html maker from the link below this post to post up a representative snapshot of your worksheet. If you have problems with the addin, search the "Questions about this board" forum first....

 

[eliW]

Hi Again,

In order to help you with this, we must know the content (and meaning) of all the cells that are included in the formulas you gave in yor question.

If you have difficulty using colo's addin, at least specify what is in all the cells (B3, B10, G6, G13 etc...)

Regards,

Eli

 

[Whisperer14]

Posted on behalf of Aviator. This is the top half of his current worksheet, I will post the next element separately. He has asked and I quote

I would like to have the ability to enter
3 FD's on Sheet 2, and 4 FD's on Sheet 3. Can you help?

WindCalculator.xls
	A	B	C	D	E	F	G
1	Station	YVR		Station	YKA		Average
2
3	FlightLevel	120
4
5	DIRECTION(TRUE)	290		DIRECTION(TRUE)	270		290
6	SPEED	50		SPEED	50		50
7	VARIATION	10		VARIATION	10
8	DIRECTION(MAG)	300		DIRECTION(MAG)	280		290
9
10	FlightLevel	220
11
12	DIRECTION(TRUE)	290		DIRECTION(TRUE)	270		290
13	SPEED	120		SPEED	75		98
14	VARIATION	10		VARIATION	10
15	DIRECTION(MAG)	300		DIRECTION(MAG)	280		290
16
17	AVERAGEDIRECTION	290		AVERAGESPEED	98
18
19
20	Computewindat	180		Computewindat	180
21	Velocity	92		Velocity	65
22	Direction	300		Direction	280
FD 2 STNS