Hi all,

One of my students, a meterologist, has asked me how to calculate
average wind direction over a period, and I am stumped. He has a column
of numbers, representing direction in degrees, and simply wants the
average direction for the column. However he is coming across what I
guess is a perennial problem, ie:

Wind Direction
355
5

where using =Average will give the result 180 - of course the real
average should be 360.

I've searched this group, and found an earlier post for a similar
problem, that suggested the formula:

=MOD(DEGREES(ATAN2(AVERAGE(COS(RADIANS(A2:A3))),AV ERAGE(SIN(RADIANS(A2:A3)))))+360,
360)

However this has only given the result 5

The recipient of this earlier post seemed happy - but I can't get it to
work!

Can anyone suggest an answer? The wind directions are simple numbers,
not formatted as degrees or anything, and it's only the average my
student is trying to obtain.

Just shows the teacher doesn't always know it all!

Phil,

For 2 directions:-
Put your larger direction in A1.
Put your smaller direction in A2
In A3
=IF(A1-A2180,(A1+A2)/2 +180,(A1+A2)/2)
If there's more than 2 directions, it gets complicated.
For 4, 8, 16, 32, etc directions, I suggest you do repeated averaging.
Average1 = (average of Direction1 & Direction2)
Average2 = (average of Direction3 & Direction4)
Overall average = (average of Average1 & Average2)

With other numbers of directions, the maths is beyond me!

BTW, what's the correct average of 90 degrees and 270 degrees?
Is it 180 degrees or 360 degrees?

Henry

"Phil" wrote in message
ups.com...
Hi all,

One of my students, a meterologist, has asked me how to calculate
average wind direction over a period, and I am stumped. He has a column
of numbers, representing direction in degrees, and simply wants the
average direction for the column. However he is coming across what I
guess is a perennial problem, ie:

Wind Direction
355
5

where using =Average will give the result 180 - of course the real
average should be 360.

I've searched this group, and found an earlier post for a similar
problem, that suggested the formula:

=MOD(DEGREES(ATAN2(AVERAGE(COS(RADIANS(A2:A3))),AV ERAGE(SIN(RADIANS(A2:A3)))))+360,
360)

However this has only given the result 5

The recipient of this earlier post seemed happy - but I can't get it to
work!

Can anyone suggest an answer? The wind directions are simple numbers,
not formatted as degrees or anything, and it's only the average my
student is trying to obtain.

Just shows the teacher doesn't always know it all!

I think the idea of these math is to said that doing a 2D (x;y) mean is
better because we do not have this 360 modulo problem (x and y are
linears). Then you can make 2 means on each axis

then you can find the atan(Y/X) of this point (and this part is not
done in the given formula, you must adapt it this way).

I tryed with your example this gives almost zero (this is a modulo 360
answer always). Think about some IF exceptions because with the sign of
X and Y you must adapt the atan function (think about the X=0 dividing also)


tryed with you example It gives almost zero (4E-15) so it's ok

You need to enter the formula with CTRL/Shift/Enter as it is an Array
formula, the zero answer is correct for 355 and 5, try other numbers
also.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=390043

SO here is the formula:

=ROUND(IF(X=0;SI(Y=0;"this wind as no favorite
direction";IF(Y0;90;-90));ATAN(Y/X)*180/PI()+IF(X<0;180;0));1)

I let you make cos and sin means (represented in this formula by x and
y) I use other cells for that purpose this make the formula readable and
also gives excel less job (because if you repeat a formula inside an
other formula i suppose he needs to make the same job many times) OF
course you can mask the unneeded cells.

for x or for y you may use matrix formula if you konw them:
=MEAN(SIN(MyDataRange*PI()/180)) in one cell and validate it with
CTRL+SHIFT+ENTER

with this you do not need to have 2 colmuns of sin and cos

notice if you keep the round function if will gives an error in case of
"no wind favorite direction"

do not round anything if you want to kkep a text warning message

well... ;) me again (2 o'clock am for me)

i also add a round (.... ; 3) around each x and y mean so the "no
favorite" case is more accurate

Phil,

Thinking about the 90 & 270 degree problem (or any 2 directions 180 degrees
apart), the correct average should be 0 degrees.
I know that 0 degrees = 360 degrees, but in this case, where the wind is
blowing from opposite directions there should be, on average, NO direction.
Imagine a helium balloon being blown for half a day in one direction, and
for the other half a day blown in the opposite direction at the same speed.
It will end up where it started. What is it's average direction for the day?

The same problem arises with 90,180, 270 & 360 degrees, if you're trying to
average 4 directions.

Henry

"Henry" wrote in message
...
Phil,

For 2 directions:-
Put your larger direction in A1.
Put your smaller direction in A2
In A3
=IF(A1-A2180,(A1+A2)/2 +180,(A1+A2)/2)
If there's more than 2 directions, it gets complicated.
For 4, 8, 16, 32, etc directions, I suggest you do repeated averaging.
Average1 = (average of Direction1 & Direction2)
Average2 = (average of Direction3 & Direction4)
Overall average = (average of Average1 & Average2)

With other numbers of directions, the maths is beyond me!

BTW, what's the correct average of 90 degrees and 270 degrees?
Is it 180 degrees or 360 degrees?

Henry

"Phil" wrote in message
ups.com...
Hi all,

One of my students, a meterologist, has asked me how to calculate
average wind direction over a period, and I am stumped. He has a column
of numbers, representing direction in degrees, and simply wants the
average direction for the column. However he is coming across what I
guess is a perennial problem, ie:

Wind Direction
355
5

where using =Average will give the result 180 - of course the real
average should be 360.

I've searched this group, and found an earlier post for a similar
problem, that suggested the formula:

=MOD(DEGREES(ATAN2(AVERAGE(COS(RADIANS(A2:A3))),AV ERAGE(SIN(RADIANS(A2:A3)))))+360,
360)

However this has only given the result 5

The recipient of this earlier post seemed happy - but I can't get it to
work!

Can anyone suggest an answer? The wind directions are simple numbers,
not formatted as degrees or anything, and it's only the average my
student is trying to obtain.

Just shows the teacher doesn't always know it all!

Phil,

Forgot to mention that due North should be entered as 360 degrees and not as
0 degrees for this to work.

Henry


"Henry" wrote in message
...
Phil,

For 2 directions:-
Put your larger direction in A1.
Put your smaller direction in A2
In A3
=IF(A1-A2180,(A1+A2)/2 +180,(A1+A2)/2)
If there's more than 2 directions, it gets complicated.
For 4, 8, 16, 32, etc directions, I suggest you do repeated averaging.
Average1 = (average of Direction1 & Direction2)
Average2 = (average of Direction3 & Direction4)
Overall average = (average of Average1 & Average2)

With other numbers of directions, the maths is beyond me!

BTW, what's the correct average of 90 degrees and 270 degrees?
Is it 180 degrees or 360 degrees?

Henry

"Phil" wrote in message
ups.com...
Hi all,

One of my students, a meterologist, has asked me how to calculate
average wind direction over a period, and I am stumped. He has a column
of numbers, representing direction in degrees, and simply wants the
average direction for the column. However he is coming across what I
guess is a perennial problem, ie:

Wind Direction
355
5

where using =Average will give the result 180 - of course the real
average should be 360.

I've searched this group, and found an earlier post for a similar
problem, that suggested the formula:

=MOD(DEGREES(ATAN2(AVERAGE(COS(RADIANS(A2:A3))),AV ERAGE(SIN(RADIANS(A2:A3)))))+360,
360)

However this has only given the result 5

The recipient of this earlier post seemed happy - but I can't get it to
work!

Can anyone suggest an answer? The wind directions are simple numbers,
not formatted as degrees or anything, and it's only the average my
student is trying to obtain.

Just shows the teacher doesn't always know it all!

Well, ok, after a shower it's better
I did it step by step to permit him to understand the steps

but if you want everything in only one formula
AND want the gestion of "no direction" error,
you'll need to add 3 more ROUND function in the "one-formula" first given:

=ROUND(MOD(ATAN2(ROUND(AVERAGE(COS(DataRange*PI()/180));3);ROUND(AVERAGE(SIN(RangeData*PI()/180));3))*180/PI()+360;360);1)

CTRL+SHIFT+ENTER

must cover every case and also gives a right DIV/O error when no
favorite direction exists ( the ROUND permit that )

about the 90 and -90 examples, it's also ok

one other method is resolve the wind direction and speed into two orthogonal
directions. i.e. east west and north south. and then find out average of
these two directional speeds and recompose into direction and speed.
the angles (direction should be converted to radians)
east west speed=scalar speed*cos (direction in
radians)--------radians=degrees*pi()/180

north south speed=scalar speed=sin(direction in rad)
once average directional speeds are found
average speed=sqrt of (averagenorthsouthspeed^2+averageeastwestspeed^2)
avaerage direction=atan(averagenorthsouthspeed/averagaeeastwestspeed)
direction will be in radians convert to degrees

see help in cos or sin or atan etc which gives how to convert degrees into
radians

this is called vector averaging.


remove $$$ from email addresss to send email
====================================
abcd wrote in message
...

well... ;) me again (2 o'clock am for me)

i also add a round (.... ; 3) around each x and y mean so the "no
favorite" case is more accurate

Many many thanks to all of you. abcd's formula works fine (after a bit
of adjusting for the British versioon of Excel!).

Henry - the correct average for 90 and 270 can either be 360 or 180,
depending on which direction the shift takes. Fortunately we rarely get
shifts of that magnitude during the periods in question (and hardly
ever in all four quadrants, thankfully!).

Again, thanks to you all - you've made a weatherman very happy!

Phil

Many thanks to all of you!

I found that abcd's formula worked best (although I had to adjust it a
little to suit the UK version of Excel, and make the rounding a bit
smaller.

Thank you for your interesting point about the average between 90 &
270, Henry. The average can be either 360 or 180 - depending on the
direction of shift (east - west or west - east). Also, the problem of
the wind blowing from all four quadrants in one period doesn't happen
very often here in the England so I hope this won't arise!

Once again, many thanks to you all - I now have one happy weatherman!

Phil