Calculating an average value of the wind direction

I've forgotton all the mathematics I ever learned relating to this sort of thing long ago.

So, here's what I got to work for me using a couple columns of logic (no radians, sin or vector required):<pre>
A1:A10 = Raw Data
B1: =IF(A1>180,A1-360,A1) <drag down to B10
C2: =IF(ABS(A1-A2)>180,1,"") <drag down to C10
D10: =IF(SUM(C1:C10)>0,IF(AVERAGE(B1:B10)<0,360+AVERAGE(B1:B10),AVERAGE(B1:B10)),AVERAGE(A1:A10))</pre>
 
OK

Seen all the replies to this post. If anyone is still interested the following VB6 code {function) works a treat.

Basically we convert all wind directions into their rectangular co-ordinates. Next we sum and average the vertical and horizontal components and from these averages we use the ArcTan function {Atn in vb} to work out the horizontal angle of the average wind. Knowing the sense {+ve \-ve} of the horizontal & vertical components we can establish in which of the 4 quadrants the wind vector is and convert the horizontal angle to a 0~359deg wind angle.

I have tested his code and it works fine across all quadrants giving accurate results.<pre>
Public Function AverageWind(CSV_WindString$)
'_______________________________________________________________________________________________________________
'__ This Function takes a CSV string of wind direction values in the range 0 ~ 259 degrees and
' returns the mean wind direction in degrees [0 ~ 359] as a numerical value {i.e. NOT a string!}

' Example of CSV_WindString$ = "170, 170, 190, 190"... this returns 180 as an average numerical value
'_______________________________________________________________________________________________________________

Wind = CSV_WindString$
p = 0
n = 0 ' Count of number of wind direction values to be averaged
pi = 22 / 7


Do ' Primary Loop to 1. extract individual wind directions and 2. to calculate their rectangular components then 3. to Average them out

'__ Get each wind direction value in turn
n = n + 1
p = InStr(Wind, ",")

If p > 0 Then
Wdir = Val(Mid$(Wind, 1, p - 1))
Wind = Trim$(Mid$(Wind, p + 1))
Else
Wdir = Val(Wind)
End If

'__ Compute Horizontal and vertical components
If Wdir >= 0 And Wdir < 90 Then Qd = 1
If Wdir >= 90 And Wdir < 180 Then Qd = 2
If Wdir >= 180 And Wdir < 270 Then Qd = 3
If Wdir >= 270 And Wdir < 360 Then Qd = 4


Select Case Qd
Case Is = 1 ' a+jb
Ang = 90 - Wdir ' Angle w.r.t. Horizontal {E\W} axis
angR = pi * Ang / 180
Case Is = 2 ' a-jb
Ang = Wdir - 90 ' Angle w.r.t. Horizontal {E\W} axis
angR = pi * Ang / 180
Case Is = 3 ' -a-jb
Ang = 270 - Wdir ' Angle w.r.t. Horizontal {E\W} axis
angR = pi * Ang / 180
Case Is = 4 ' -a+jb
Ang = Wdir - 270 ' Angle w.r.t. Horizontal {E\W} axis
angR = pi * Ang / 180
End Select

a = Cos(angR) ' Horizontal component of wind vector
b = Sin(angR) ' Vertical component of wind vector

'__ Correct Quadrant Signage of the wind vector's horizontal [a] & vertical components
Select Case Qd
Case Is = 1 ' All [a&b] +ve
a = a
b = b
Case Is = 2 ' Horizontal [a] +ve, Vertical -ve
a = a
b = -b
Case Is = 3 ' Both [a&b] -ve
a = -a
b = -b
Case Is = 4 ' Horizontal [a] -ve; Vertical +ve
a = -a
b = b

End Select

'__ Calculate Cumulative values of horizontal [CuA] and vertical [CuB] components of wind angles
CuA = CuA + a ' Cumulative sum of all the horizontal components of each wind vector being averaged.
CuB = CuB + b ' Cumulative sum of all the vertical components of each wind vector being averaged.

'__Prevent division by zero in event of either value being exactly zero
If CuA = 0 Then CuA = 0.0000001


Loop While p <> 0 ' END of primary loop



'__ Calculate resulting horizontal angle [Ang] of cumulative sums of vertical and horizontal components of all wind vectors
Ang = Int(180 * (Atn(Abs(CuB / n) / Abs(CuA / n))) / pi + 0.5)


'__ As [Ang] if the horizontal angle relative to the E\W axis of the wind rose, the wind direction is calculated from
' the Quadrant indicated by the signage of components CuA and CuB as below:-
If CuA > 0 And CuB > 0 Then WindDir = 90 - Ang: Q = 1 ' i.e. Both CuA & CuB are +ve indicating Quadrant #1
If CuA > 0 And CuB < 0 Then WindDir = 90 + Ang: Q = 2 ' i.e. CuA is +ve & CuB is -ve indicating Quadrant #2
If CuA < 0 And CuB < 0 Then WindDir = 270 - Ang: Q = 3 ' i.e. Both CuA & CuB are -ve indicating Quadrant #3
If CuA < 0 And CuB > 0 Then WindDir = 270 + Ang: Q = 4 ' i.e. CuA is -ve & CuB is +ve indicating Quadrant #4

If WindDir = 360 Then WindDir = 0 ' Occasionally the above code returns WindDir as 360. This corrects it to zero!

Quadrant = Q ' Not used in Function ~ For Tracking info only!

AverageWind = WindDir

End Function</pre>
 
>Ok, how it works if you have, for example:
>
>(90+90+90+90+90) modulo 360) / 5 = 18 (incorrect)
>(90+90+90+90+90) / 5 = 90 (correct).
>
>The method that you proposed doesn't work!

(90 mod 360 +
90 mod 360 +
90 mod 360 +
90 mod 360 +
90 mod 360) / 5 = 90
 
You need to use modulo 360 arithmetic. Modulo 360 is simply the remainder after dividing the sum by 360. E.g.

(10 + 10 + 10 + 350) / 4 = 95 (incorrect)

((10 + 10 + 10 + 350) modulo 360) / 4 = 5 (correct)

Also - don't allow 360 as a valid reading. It aliases with 0 and is therefore ambiguous.

I am trying to get an average wind direction for this spreadsheet. Each entry is the wind direction at the hour and half hour for a day. How would I do this?
 

Attachments

How would I calculate the average wind direction using these values? The data in this file are wind directions taking at the hour and half hour of a day.
 

Attachments

Top