Calculating wind speed from 3 components (U, V, and W)

[oliver_1984]

Hello all,

I am having an issue! I have a 3D sonic anemometer, and this is the first time I deal with it. I did a small experiment to collect some data. The output was U, V, and W for every 0.3 second. I have a huge excel sheet of data, and I don't know how to measure wind speed and direction from the output. The wind that day was south wind. Please any help is highly appreciated!

Thanks!

 

[Kenneth Hobson]

Welcome to the forum!

I would suggest that you read the manual. There are also papers that talk about various factors that you may want to consider. https://www.ecn.nl/docs/library/report/2001/c01102.pdf

If you have formulas with known inputs and a known output, we can help more.

 

[oliver_1984]

Thank you Mr. Hobson. I honestly have no clue about any formula, since this is the first time I deal with creating wind map or wind field from an instrument like the 3D sonic anemometer. I know it sounds stupid, but this is what I am really going through. All what I have is an excel sheet of data with U, V, and W values (for example: U=-0.05, V=0.83, and W=-1.17, at hour 12, minute 56, and 46.333 second). Every 0.33 second I have a row of data. So a 15 minutes of work produced a big excel sheet of data!

 

[Kenneth Hobson]

There are an infinite number of ways to compute x,y,z into what you may need. If you don't know what the 3 inputs mean, most of us would not either. As Mr. Scott would say, I am an Engineer, not a meteorologist.

As the paper that I referenced explained, there is a huge amount of data. Access is probably the better tool for collecting data and even that might not be sufficient. As the paper explained, they averaged the speed over 15 minute intervals. Even that is prone to error as wind does not change direction or speed on our time.

 

[oliver_1984]

Well, from the article it looks very easy! The equation is "sqrt (u^2+v^2 +w^2)"! Thanks you Mr. Hobson, I do appreciate your valuable input

 

[Kenneth Hobson]

NP. Did you need more help?

It should be easy knowing that formula. You can add another column to do that and then take an average for some interval.

I would suggest looking at the data and especially the computed value. You may find some outliers that won't make sense like -1 mph or 1000 mph that need to be removed. I would use ASTM E 178 and a 2.5% confidence limit for something like that maybe. I have an Excel routine that uses that method. If you find the need, I can post it.

 

[oliver_1984]

The equation is good for wind speed, but I still need a formula for wind direction using the three vectors, I found many formulas using only 2 vectors, but nothing for the three! I would really appreciate the excel routine you have, it will really help me a lot!
Thanks!

 

[Kenneth Hobson]

I figured as much. You might search for "3D sonic anemometer" to find other papers. Most will probably not be free which is why I posted an older one. Even so, you might find one to get an equation. This looks promising: http://onlinelibrary.wiley.com/doi/10.1002/we.1965/full

These t test routines should be easy to understand if you know ASTM E 178. Change the 0.01 to suit. I would look at your data first to see if it is even needed though. For some of my data, I mark outliers in red fill so that the user can determine if they really want to delete it.

Sub Test_TStat()
  Debug.Print TStat(Range("b2:b16"), Range("c2:c4"))
End Sub

Sub Test_TCrit()
  Debug.Print TCrit(Range("b2:b16"), Range("c2:c4"))
End Sub

Sub Test_TestPass()
  Debug.Print TTestPass(Range("b2:b16"), Range("c2:c4"))
End Sub

Function TTestPass(set1 As Range, set2 As Range) As Boolean
  Application.Volatile (False)
  Dim ts As Double, tc As Double
  On Error Resume Next
  ts = TStat(set1, set2)
  tc = TCrit(set1, set2)
  'Debug.Print "TStat: " & ts, "TCrit: " & tc
  TTestPass = ts <= tc
End Function

Function TCrit(set1 As Range, set2 As Range, _
  Optional tfPaired As Boolean = False) As Double
  Application.Volatile (False)
  Dim df1 As Integer, df2 As Integer, df As Integer
  On Error Resume Next
  df1 = WorksheetFunction.Count(set1)
  df2 = WorksheetFunction.Count(set2)
  df = df1 + df2 - 2
  If tfPaired = True Then df = df1 - 1
  TCrit = WorksheetFunction.T_Inv_2T(0.01, df)
End Function

Function TStat(set1 As Range, set2 As Range) As Double
  Application.Volatile (False)
  Dim var1 As Double, var2 As Double
  Dim sp2 As Double, df1 As Integer, df2 As Integer
  Dim avg1 As Double, avg2 As Double
  On Error Resume Next
  var1 = WorksheetFunction.Var_S(set1)
  var2 = WorksheetFunction.Var_S(set2)
  df1 = WorksheetFunction.Count(set1) - 1
  df2 = WorksheetFunction.Count(set2) - 1
  sp2 = (var1 * df1 + var2 * df2) / (df1 + df2)
  avg1 = WorksheetFunction.Average(set1)
  avg2 = WorksheetFunction.Average(set2)
  'Debug.Print "var1: " & var1, "var2: " & var2
  'Debug.Print "df1: ", df1, "df2: " & df2
  'Debug.Print "sp2: ", sp2, "avg1: " & avg1, "avg2: " & avg2
  TStat = Abs(avg1 - avg2) / (sp2 / (df1 + 1) + sp2 / (df2 + 1)) ^ 0.5
End Function