Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    New Member
    Join Date
    Nov 2011
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular Jeffrey Smith's Avatar
    Join Date
    Feb 2005
    Location
    Bellingham, Washington
    Posts
    795
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)
    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 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
    ---Using xl2010 - Win7 64 ---
    ***Please assume positive intent and a helpful disposition from all my posts***
    >> Available Mon-Fri until 3:30 Pacific

  3. #3
    Board Regular Jeffrey Smith's Avatar
    Join Date
    Feb 2005
    Location
    Bellingham, Washington
    Posts
    795
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating Average wind speed and direction

    I guess all that work I put into solving the problem was ignored
    ---Using xl2010 - Win7 64 ---
    ***Please assume positive intent and a helpful disposition from all my posts***
    >> Available Mon-Fri until 3:30 Pacific

  4. #4
    New Member
    Join Date
    Nov 2011
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating Average wind speed and direction

    Quote Originally Posted by Jeffrey Smith View Post
    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. #5
    Board Regular Jeffrey Smith's Avatar
    Join Date
    Feb 2005
    Location
    Bellingham, Washington
    Posts
    795
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    ---Using xl2010 - Win7 64 ---
    ***Please assume positive intent and a helpful disposition from all my posts***
    >> Available Mon-Fri until 3:30 Pacific

  6. #6
    New Member
    Join Date
    Mar 2013
    Location
    AZ
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)
    Last edited by Chan Weinmeister; Mar 1st, 2013 at 09:43 PM. Reason: Clarification

  7. #7
    Board Regular Jeffrey Smith's Avatar
    Join Date
    Feb 2005
    Location
    Bellingham, Washington
    Posts
    795
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    ---Using xl2010 - Win7 64 ---
    ***Please assume positive intent and a helpful disposition from all my posts***
    >> Available Mon-Fri until 3:30 Pacific

  8. #8
    Board Regular Jeffrey Smith's Avatar
    Join Date
    Feb 2005
    Location
    Bellingham, Washington
    Posts
    795
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
      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
    ---Using xl2010 - Win7 64 ---
    ***Please assume positive intent and a helpful disposition from all my posts***
    >> Available Mon-Fri until 3:30 Pacific

  9. #9
    Board Regular Jeffrey Smith's Avatar
    Join Date
    Feb 2005
    Location
    Bellingham, Washington
    Posts
    795
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    ---Using xl2010 - Win7 64 ---
    ***Please assume positive intent and a helpful disposition from all my posts***
    >> Available Mon-Fri until 3:30 Pacific

  10. #10
    Board Regular Jeffrey Smith's Avatar
    Join Date
    Feb 2005
    Location
    Bellingham, Washington
    Posts
    795
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by Jeffrey Smith; Mar 21st, 2013 at 11:34 AM.
    ---Using xl2010 - Win7 64 ---
    ***Please assume positive intent and a helpful disposition from all my posts***
    >> Available Mon-Fri until 3:30 Pacific

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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