# Thread: Calculating Average wind speed and direction Thanks: 0 Likes: 0

1. ## Calculating Average wind speed and direction

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 > (meteorological wind direction (wdir): eg E=90, S=180, W=270) > (1) calculate u and v > u = -wspd*sin(wdir) > v = -wspd*cos(wdir) > > (2) calculate the daily mean of each wind component: > uave, vave > > (3) transform to wspd and wdir > rad = 4.0*atan (1.0)/180. 0.017453 > WSPD = sqrt(uAve^2+vAve^2) > WDIR = atan2(uAve,vAve)/rad +180. ; mean wind direction [0,360] >

2. ## Re: Calculating Average wind speed and direction

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

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

3. ## Re: Calculating Average wind speed and direction

I guess all that work I put into solving the problem was ignored

4. ## Re: Calculating Average wind speed and direction

Originally Posted by Jeffrey Smith
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

5. ## Re: Calculating Average wind speed and direction

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.

6. ## Re: Calculating Average wind speed and direction

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

7. ## Re: Calculating Average wind speed and direction

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)
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)

 R S T U V Wind Speed Wind Direction Weighted Count Discounted Speed 9 2.62 146.59 0.174 0.371 10 3.80 227.76 0.252 3.800 11 3.96 207.28 0.263 3.693 12 4.27 131.76 0.283 0.498 13 4.40 5.65 0.292 3.228 14 5.33 247.87 0.354 5.027 15 5.42 111.83 0.360 2.429 16 6.21 341.56 0.412 2.437 17 6.45 241.80 0.428 6.276 18 7.80 254.33 0.518 7.018 19 8.02 187.06 0.532 6.017 20 9.22 329.24 0.612 1.726 21 9.87 288.18 0.655 4.976 22 9.88 200.27 0.656 8.709 23 10.74 320.15 0.713 0.318 24 11.00 281.36 0.730 6.634 25 12.62 213.19 0.837 12.175 26 12.66 155.24 0.840 3.656 27 13.40 242.27 0.889 13.012 28 15.07 207.99 1.000 14.119 29 30 Average: 8.14 217.07 5.31 31 32 Weighted: 228.45 6.51

8. ## Re: Calculating Average wind speed and direction

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

Worksheet Formulas
CellFormula
D4=VelX(B4,C4)
E4=VelY(B4,C4)
F4=PolarCoord(D4,E4)
B26=AVERAGE(B4:B24)
C26=AVERAGE(C4:C24)
D26=AVERAGE(D4:D24)
E26=AVERAGE(E4:E24)
B29=SQRT(D26^2+E26^2)
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

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

End Function```

9. ## Re: Calculating Average wind speed and direction

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```

10. ## Re: Calculating Average wind speed and direction

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:
 Mag Dir 5 90 5 270

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:
 Mag Dir 5 90 10 270

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•