Calculating Average wind speed and direction

HAFEDH78

New Member
Joined
Nov 21, 2011
Messages
5
Hi Everyone,

I was wondering if someone could check if the following formulas make sense. I am trying to calculate the daily average wind speed and wind direction derived from values taken at 6hr intervals.
From wind speed and direction
</SPAN>
> (meteorological wind direction (wdir): eg E=90, S=180, W=270)
</SPAN>
> (1) calculate u and v </SPAN>
> u = -wspd*sin(wdir) </SPAN>
> v = -wspd*cos(wdir) </SPAN>
> </SPAN>
> (2) calculate the daily mean of each wind component: </SPAN>
> uave, vave </SPAN>
> </SPAN>
> (3) transform to wspd and wdir </SPAN>
> rad = 4.0*atan (1.0)/180.
</SPAN>
0.017453</SPAN>
> WSPD = sqrt(uAve^2+vAve^2)
</SPAN>
> WDIR = atan2(uAve,vAve)/rad +180. ; mean wind direction [0,360] </SPAN>
> </SPAN>
</SPAN>
</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=6></COLGROUP>
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Can I open a discussion on this. I cannot agree with those formulas based on some tests I ran. I sorted a list of wind speeds and wind directions. I use a weighted average method to calculate average wind direction. I calculate my average speed by taking the weighted average of discounted velocities. Discounted means to me that the velocity is reduced based on the angle to the weighted direction. I will show my formulas and would like to see if anybody has a better idea.

Columns Q and R are given values.

The formula in cell S9 is: =INT(Q9/MAX($Q$9:$Q$28)*1000)
Cell T9 formula: =ABS(COS(RADIANS(R9-WeightedDir))*Q9)
Row 30 are averages for each column
Cell S32 formula: =SUMPRODUCT(S9:S28,R9:R28)/(SUM(S9:S28)) (WeightedDir)
Cell S33 formula: =SUMPRODUCT(S9:S28,T9:T28)/SUM(S9:S28)

Other test
Cell U9 formula: =Q9*SIN(R9)
Cell V9 formula: =Q9*COS(R9)
Cell V32 formula: =SQRT(U30^2+V30^2)
Cell V33 formula: =4*ATAN(1)/180
Cell V34 formula: =ATAN2(U30,V30)/V33+180


Q</SPAN>R</SPAN>S</SPAN>T</SPAN>U</SPAN>V</SPAN>
Wind Speed</SPAN>Wind Direction</SPAN>Weighted Count</SPAN>Discounted Speed</SPAN>u</SPAN>v</SPAN>
9</SPAN>2.62</SPAN>146.59</SPAN>173</SPAN>0.37</SPAN>2.291763</SPAN>-1.26973</SPAN>
10</SPAN>3.80</SPAN>227.76</SPAN>252</SPAN>3.80</SPAN>3.799943</SPAN>0.020776</SPAN>
11</SPAN>3.96</SPAN>207.28</SPAN>262</SPAN>3.69</SPAN>-0.25767</SPAN>3.951608</SPAN>
12</SPAN>4.27</SPAN>131.76</SPAN>283</SPAN>0.50</SPAN>-0.79339</SPAN>4.195645</SPAN>
13</SPAN>4.40</SPAN>5.65</SPAN>291</SPAN>3.23</SPAN>-2.60355</SPAN>3.547046</SPAN>
14</SPAN>5.33</SPAN>247.87</SPAN>353</SPAN>5.03</SPAN>1.655475</SPAN>-5.06639</SPAN>
15</SPAN>5.42</SPAN>111.83</SPAN>359</SPAN>2.43</SPAN>-5.17235</SPAN>1.61963</SPAN>
16</SPAN>6.21</SPAN>341.56</SPAN>412</SPAN>2.44</SPAN>4.760865</SPAN>-3.98726</SPAN>
17</SPAN>6.45</SPAN>241.80</SPAN>428</SPAN>6.28</SPAN>0.66083</SPAN>-6.41606</SPAN>
18</SPAN>7.80</SPAN>254.33</SPAN>517</SPAN>7.02</SPAN>1.08075</SPAN>-7.72476</SPAN>
19</SPAN>8.02</SPAN>187.06</SPAN>532</SPAN>6.02</SPAN>-7.94677</SPAN>1.081299</SPAN>
20</SPAN>9.22</SPAN>329.24</SPAN>611</SPAN>1.72</SPAN>5.411247</SPAN>-7.46504</SPAN>
21</SPAN>9.87</SPAN>288.18</SPAN>654</SPAN>4.98</SPAN>-7.39245</SPAN>6.539768</SPAN>
22</SPAN>9.88</SPAN>200.27</SPAN>655</SPAN>8.71</SPAN>-7.0317</SPAN>6.940435</SPAN>
23</SPAN>10.74</SPAN>320.15</SPAN>712</SPAN>0.32</SPAN>-3.09634</SPAN>10.28398</SPAN>
24</SPAN>11.00</SPAN>281.36</SPAN>729</SPAN>6.64</SPAN>-10.8073</SPAN>2.049977</SPAN>
25</SPAN>12.62</SPAN>213.19</SPAN>837</SPAN>12.17</SPAN>-5.35594</SPAN>11.42709</SPAN>
26</SPAN>12.66</SPAN>155.24</SPAN>840</SPAN>3.65</SPAN>-12.2053</SPAN>-3.36262</SPAN>
27</SPAN>13.40</SPAN>242.27</SPAN>889</SPAN>13.01</SPAN>-4.81272</SPAN>-12.5059</SPAN>
28</SPAN>15.07</SPAN>207.99</SPAN>1000</SPAN>14.12</SPAN>9.058674</SPAN>12.04348</SPAN>
29</SPAN>
30</SPAN>8.14</SPAN>217.1</SPAN>5.31</SPAN>-1.93779</SPAN>0.795148</SPAN>
31</SPAN>
32</SPAN>Weighted Direction:</SPAN>228.47</SPAN>WSPD</SPAN>2.09459</SPAN>
33</SPAN>Weighted Speed:</SPAN>6.51</SPAN>rad</SPAN>0.017453</SPAN>
34</SPAN>WDIR</SPAN>337.6899</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=3><COL><COL><COL span=2></COLGROUP>



If I look at the highest velocities, they should drive the average wind speed. So the direction that the highest speeds are traveling will dominate the average direction. If I artificially inflate any given wind speed in the list to a value 1000 times higher I expect the average speed to be very near the inflated value. Just as I expect the direction for that inflated value to dominate the average direction. My calculations show that to be true.

Q</SPAN>R</SPAN>S</SPAN>T</SPAN>U</SPAN>V</SPAN>
Wind Speed</SPAN>Wind Direction</SPAN>Weighted Count</SPAN>Discounted Speed</SPAN>u</SPAN>v</SPAN>
9</SPAN>2.62</SPAN>146.59</SPAN>1</SPAN>1.16</SPAN>2.291763</SPAN>-1.26973</SPAN>
10</SPAN>3.80</SPAN>227.76</SPAN>2</SPAN>3.62</SPAN>3.799943</SPAN>0.020776</SPAN>
11</SPAN>3.96</SPAN>207.28</SPAN>2</SPAN>3.95</SPAN>-0.25767</SPAN>3.951608</SPAN>
12</SPAN>4.27</SPAN>131.76</SPAN>2</SPAN>0.86</SPAN>-0.79339</SPAN>4.195645</SPAN>
13</SPAN>4.40</SPAN>5.65</SPAN>2</SPAN>4.00</SPAN>-2.60355</SPAN>3.547046</SPAN>
14</SPAN>5.33</SPAN>247.87</SPAN>3</SPAN>4.22</SPAN>1.655475</SPAN>-5.06639</SPAN>
15</SPAN>5.42</SPAN>111.83</SPAN>3</SPAN>0.79</SPAN>-5.17235</SPAN>1.61963</SPAN>
16</SPAN>6.21</SPAN>341.56</SPAN>4</SPAN>4.10</SPAN>4.760865</SPAN>-3.98726</SPAN>
17</SPAN>6.45</SPAN>241.80</SPAN>4</SPAN>5.49</SPAN>0.66083</SPAN>-6.41606</SPAN>
18</SPAN>7.80</SPAN>254.33</SPAN>5</SPAN>5.60</SPAN>1.08075</SPAN>-7.72476</SPAN>
19</SPAN>8.02</SPAN>187.06</SPAN>5</SPAN>7.37</SPAN>-7.94677</SPAN>1.081299</SPAN>
20</SPAN>9.22</SPAN>329.24</SPAN>6</SPAN>4.48</SPAN>5.411247</SPAN>-7.46504</SPAN>
21</SPAN>9.87</SPAN>288.18</SPAN>6</SPAN>2.05</SPAN>-7.39245</SPAN>6.539768</SPAN>
22</SPAN>9.88</SPAN>200.27</SPAN>6</SPAN>9.73</SPAN>-7.0317</SPAN>6.940435</SPAN>
23</SPAN>10.74</SPAN>320.15</SPAN>7</SPAN>3.67</SPAN>-3.09634</SPAN>10.28398</SPAN>
24</SPAN>11.00</SPAN>281.36</SPAN>7</SPAN>3.55</SPAN>-10.8073</SPAN>2.049977</SPAN>
25</SPAN>12.62</SPAN>213.19</SPAN>8</SPAN>12.60</SPAN>-5.35594</SPAN>11.42709</SPAN>
26</SPAN>12.66</SPAN>155.24</SPAN>8</SPAN>7.27</SPAN>-12.2053</SPAN>-3.36262</SPAN>
27</SPAN>13.40</SPAN>242.27</SPAN>8</SPAN>11.35</SPAN>-4.81272</SPAN>-12.5059</SPAN>
28</SPAN>1507.00</SPAN>207.99</SPAN>1000</SPAN>1505.88</SPAN>905.8674</SPAN>1204.348</SPAN>
29</SPAN>
30</SPAN>82.73</SPAN>217.1</SPAN>80.09</SPAN>42.90264</SPAN>60.41036</SPAN>
31</SPAN>
32</SPAN>Weighted Direction:</SPAN>210.20</SPAN>WSPD</SPAN>74.09486</SPAN>
33</SPAN>Weighted Speed:</SPAN>1383.31</SPAN>rad</SPAN>0.017453</SPAN>
34</SPAN>WDIR</SPAN>234.6181</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=3><COL><COL><COL span=2></COLGROUP>
 
Upvote 0
I guess all that work I put into solving the problem was ignored :(

Hi Jeffrey,

Sorry I didn't answer back to your solution, it actually made rethink my maths and then found out that there was some other ways of calculating average wind speed.... And didn't help when i got handed a spreadsheet with several wind directions and speeds taken at diffrent times with 3 min interval between each reading... Oo

So I am still working on it.

Anyway, I am really greatful for all your help.

Thanks

H
 
Upvote 0
Thanks for the response H. I'm interested in your findings if you would like to share. Most of my work is analyzing velocity data from acoustic dopplers. I also have installed and analyzed data from weather stations, although not recently. So my work is right along your question line.
 
Upvote 0
I stumbled on this thread yesterday when I was looking for ideas on how to average a range of wind direction/speeds. I came up with bad results myself.... Any help on squaring the trig on this would be very helpful.... This is all based on par 1.1 of the following doc -> http://www.h2ns.com/media/tech-notes/tn09.pdf


DIR
Speed
SIN(DIR)</SPAN>
COS(DIR)</SPAN>
U</SPAN>
V</SPAN>
360</SPAN>
20</SPAN>
0.958916</SPAN>
-0.28369109</SPAN>
19.17831</SPAN>
-5.67382</SPAN>
360</SPAN>
20</SPAN>
0.958916</SPAN>
-0.28369109</SPAN>
19.17831</SPAN>
-5.67382</SPAN>
360</SPAN>
25</SPAN>
0.958916</SPAN>
-0.28369109</SPAN>
23.97289</SPAN>
-7.09228</SPAN>
360</SPAN>
25</SPAN>
0.958916</SPAN>
-0.28369109</SPAN>
23.97289</SPAN>
-7.09228</SPAN>
360</SPAN>
25</SPAN>
0.958916</SPAN>
-0.28369109</SPAN>
23.97289</SPAN>
-7.09228</SPAN>
360</SPAN>
25</SPAN>
0.958916</SPAN>
-0.28369109</SPAN>
23.97289</SPAN>
-7.09228</SPAN>
10</SPAN>
25</SPAN>
-0.54402</SPAN>
-0.83907153</SPAN>
-13.6005</SPAN>
-20.9768</SPAN>
10</SPAN>
25</SPAN>
-0.54402</SPAN>
-0.83907153</SPAN>
-13.6005</SPAN>
-20.9768</SPAN>
10</SPAN>
25</SPAN>
-0.54402</SPAN>
-0.83907153</SPAN>
-13.6005</SPAN>
-20.9768</SPAN>
AVERAGE</SPAN>
0.457937</SPAN>
-0.4688179</SPAN>
10.38296</SPAN>
-11.4052</SPAN>
15.42353</SPAN>
=(average U^2+ average V^2)^(1/2)</SPAN>
-0.73851</SPAN>
=ATAN(average U/average V)</SPAN>
-42.3137</SPAN>
=DEGREES(radians above)</SPAN>

<TBODY>
</TBODY>
 
Last edited:
Upvote 0
This is what I came up with by myself. I use a weighted average approach. I would like to have anybody weigh in on this. Tell me how this works for you. I put the average values to show comparison. 6.51 fps is the solution for the table below. I sorted the table by wind speed so I could analyze the results better.

Jeff



Cell U9: =$S9/MAX($S$9:$S$28)
Cell V9: =ABS(COS(RADIANS($T9-WeightedDir))*$S9)
Range Name WeightedDir = Cell U32
Cell U32: =SUMPRODUCT(U9:U28,T9:T28)/(SUM(U9:U28))
Cell V32: =SUMPRODUCT(U9:U28,V9:V28)/SUM(U9:U28)

RSTUV
Wind SpeedWind DirectionWeighted CountDiscounted Speed
92.62146.590.1740.371
103.80227.760.2523.800
113.96207.280.2633.693
124.27131.760.2830.498
134.405.650.2923.228
145.33247.870.3545.027
155.42111.830.3602.429
166.21341.560.4122.437
176.45241.800.4286.276
187.80254.330.5187.018
198.02187.060.5326.017
209.22329.240.6121.726
219.87288.180.6554.976
229.88200.270.6568.709
2310.74320.150.7130.318
2411.00281.360.7306.634
2512.62213.190.83712.175
2612.66155.240.8403.656
2713.40242.270.88913.012
2815.07207.991.00014.119
29
30Average:8.14217.075.31
31
32Weighted:228.456.51

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Ok, Ignore all previous posts from me on this. Please look at this solution.

I created some user defined functions that help with the flow. Otherwise the if statements in the cells would get quite cumbersome. This converts Magnitude and Direction into Velocity components X (East+-) and Y (North+-). Then you average those values for the set. Use PolarCoord to convert the X/Y components into direction again. Using this method allows for the magnitude to be considered into the direction. Given that some wind readings have more magnitude, so they should be weighted more than readings with less magnitude. The Weighted Velocity is using discounted velocities. Meaning, when a reading is angled to the mean direction, it doesn't flow directly toward the mean point.

Jeff


Excel 2010
ABCDEF
3MagnitudeDirectionVel XVel YDouble Check Coordinate
45.00100.004.92-0.87100.00
55.0014.001.214.8514.00
65.0013.001.124.8713.00
75.0012.001.044.8912.00
85.0011.000.954.9111.00
95.0010.000.874.9210.00
105.009.000.784.949.00
115.008.000.704.958.00
125.007.000.614.967.00
135.006.000.524.976.00
145.005.000.444.985.00
155.004.000.354.994.00
165.003.000.264.993.00
175.002.000.175.002.00
185.001.000.095.001.00
195.000.000.005.00360.00
205.00359.00-0.095.00359.00
215.00353.00-0.614.96353.00
225.00352.00-0.704.95352.00
235.00351.00-0.784.94351.00
2410.00350.00-1.749.85350.00
25
26Average:5.2493.810.484.91
27
28Weighted VelWeighted Dir
294.935.61
Weighted_Dir
Cell Formulas
RangeFormula
D4=VelX(B4,C4)
D26=AVERAGE(D4:D24)
E4=VelY(B4,C4)
E26=AVERAGE(E4:E24)
F4=PolarCoord(D4,E4)
B26=AVERAGE(B4:B24)
B29=SQRT(D26^2+E26^2)
C26=AVERAGE(C4:C24)
C29=PolarCoord(D26,E26)


This is for calculating the Velocity X from the magnitude and Direction
Code:
Function VelX(M As Range, D As Range) As Single


  Dim pi As Double
  Dim Mag As Double
  Dim Dir As Double
  
  Mag = M.Value
  Dir = D.Value
  pi = Atn(1) * 4
  
  VelX = Mag * Cos(2 * pi * (90 - Dir) / 360)


End Function


This is for calculating the Velocity Y from the Magnitude and Direction
Code:
Function VelY(M As Range, D As Range) As Single


  Dim pi As Double
  Dim Mag As Double
  Dim Dir As Double
  
  Mag = M.Value
  Dir = D.Value
  pi = Atn(1) * 4
  
  VelY = Mag * Sin(2 * pi * (90 - Dir) / 360)


End Function


This is for calculating the degrees from Velocity X and Velocity Y values
Code:
Function PolarCoord(Xrng As Range, Yrng As Range) As Single


  Dim rAlpha As Single
  Dim x As Double
  Dim y As Double
  Dim pi As Double
  
  x = Xrng.Value
  y = Yrng.Value
  pi = Atn(1) * 4
  
  If x = 0 Then
    If y = 0 Then
      rAlpha = 90
    Else
      rAlpha = 90 - ((y / y) * 90)
    End If
  Else
    rAlpha = 360 + (180 / pi * ATAN2(y, x))
  End If
      
  If rAlpha < 0 Then
      rAlpha = rAlpha + 360
  ElseIf rAlpha > 360 Then
    rAlpha = rAlpha - 360
  End If
    
  PolarCoord = rAlpha
  
End Function


I just like using a ATAN2 built in function instead of using worksheetfunction
Code:
Function ATAN2(ByVal y As Double, ByVal x As Double) As Double
  Dim Rad As Double
  
  If x = 0 Then
    Rad = 2 * Atn((Sqr(x * x + y * y) - y))
  Else
    Rad = 2 * Atn((Sqr(x * x + y * y) - y) / x)
  End If
  If Rad < 0 Then Rad = Rad + pi * 2


  ATAN2 = Rad
End Function
 
Upvote 0
Please use the PolarCoord function below. One minor fix.

Code:
Function PolarCoord(Xrng As Range, Yrng As Range) As Single

  Dim rAlpha As Single
  Dim x As Double
  Dim y As Double
  Dim pi As Double
  
  x = Xrng.Value
  y = Yrng.Value
  pi = Atn(1) * 4
  
  If x = 0 Then
    If y = 0 Then
      rAlpha = 0
    Else
      rAlpha = 90 - ((y / y) * 90)
    End If
  Else
    rAlpha = 360 + (180 / pi * ATAN2(y, x))
  End If
      
  If rAlpha < 0 Then
      rAlpha = rAlpha + 360
  ElseIf rAlpha > 360 Then
    rAlpha = rAlpha - 360
  End If
    
  PolarCoord = rAlpha
  
End Function
 
Upvote 0
The fix above corrected a problem when all wind speeds canceled each other out (both X and Y were zero).

In my opinion, and several other opinions I have seen on line, summing all the wind speed data and dividing by the number of points is NOT an accurate way to calculate average wind speed. This does not allow for wind direction. If all the wind recordings were going in the same direction, that method would work.


If you have two recordings with equal magnitude, but going in the opposite direction, what would your answer be. Based on the average of the two points it would be wrong. The two magnitudes would cancel themselves out. For Example:
MagDir
590
5270

<tbody>
</tbody>



Sum = 10
10 divided by 2 is 5 < wrong
The mean wind speed should be zero
5 - 5 = 0
0 / 2 = 0

A wind speed magnitude higher than others should be given more weight. For example:
MagDir
590
10270

<tbody>
</tbody>

Sum = 15
15 divided by 2 is 7.5 < wrong
The mean wind speed should be 2.5
10 - 5 = 5
5 / 2 = 2.5

Of course this simple math only works with two wind speeds going in opposite directions.

Jeff
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top