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

QRSTUVWind Speed Wind Direction Weighted Count Discounted Speed u v 92.62 146.59 173 0.37 2.291763 -1.26973 103.80 227.76 252 3.80 3.799943 0.020776 113.96 207.28 262 3.69 -0.25767 3.951608 124.27 131.76 283 0.50 -0.79339 4.195645 134.40 5.65 291 3.23 -2.60355 3.547046 145.33 247.87 353 5.03 1.655475 -5.06639 155.42 111.83 359 2.43 -5.17235 1.61963 166.21 341.56 412 2.44 4.760865 -3.98726 176.45 241.80 428 6.28 0.66083 -6.41606 187.80 254.33 517 7.02 1.08075 -7.72476 198.02 187.06 532 6.02 -7.94677 1.081299 209.22 329.24 611 1.72 5.411247 -7.46504 219.87 288.18 654 4.98 -7.39245 6.539768 229.88 200.27 655 8.71 -7.0317 6.940435 2310.74 320.15 712 0.32 -3.09634 10.28398 2411.00 281.36 729 6.64 -10.8073 2.049977 2512.62 213.19 837 12.17 -5.35594 11.42709 2612.66 155.24 840 3.65 -12.2053 -3.36262 2713.40 242.27 889 13.01 -4.81272 -12.5059 2815.07 207.99 1000 14.12 9.058674 12.04348 29308.14 217.15.31-1.93779 0.795148 3132Weighted Direction:228.47WSPD 2.09459 33Weighted Speed:6.51rad 0.017453 34WDIR 337.6899

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.

QRSTUVWind Speed Wind Direction Weighted Count Discounted Speed u v 92.62 146.59 1 1.16 2.291763 -1.26973 103.80 227.76 2 3.62 3.799943 0.020776 113.96 207.28 2 3.95 -0.25767 3.951608 124.27 131.76 2 0.86 -0.79339 4.195645 134.40 5.65 2 4.00 -2.60355 3.547046 145.33 247.87 3 4.22 1.655475 -5.06639 155.42 111.83 3 0.79 -5.17235 1.61963 166.21 341.56 4 4.10 4.760865 -3.98726 176.45 241.80 4 5.49 0.66083 -6.41606 187.80 254.33 5 5.60 1.08075 -7.72476 198.02 187.06 5 7.37 -7.94677 1.081299 209.22 329.24 6 4.48 5.411247 -7.46504 219.87 288.18 6 2.05 -7.39245 6.539768 229.88 200.27 6 9.73 -7.0317 6.940435 2310.74 320.15 7 3.67 -3.09634 10.28398 2411.00 281.36 7 3.55 -10.8073 2.049977 2512.62 213.19 8 12.60 -5.35594 11.42709 2612.66 155.24 8 7.27 -12.2053 -3.36262 2713.40 242.27 8 11.35 -4.81272 -12.5059 281507.00 207.99 1000 1505.88 905.8674 1204.348 293082.73 217.180.0942.90264 60.41036 3132Weighted Direction:210.20WSPD 74.09486 33Weighted Speed:1383.31rad 0.017453 34WDIR 234.6181

## Like this thread? Share it with others