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>