Average Wind Direction

[rexmorgan]

I am attempting to create a custom function using other functions i
EXCEL. I am attempting to take an array of data (range of cells
perform a few calculations and return the results. The function will b
stored in the appropriate workbook.

Here is the Code

Function AvgWndDir(cellrange)
Radianz = WorksheetFunction.Radians(cellrange)
' Convert from degrees to Radians
SINZ = WorksheetFunction.Sin(Radianz)
' Take the SIN of the Radians
AVGS = WorksheetFunction.Average(SINZ)
'Average the Sines
INVSIN = WorksheetFunction.Asin(AVGS)
' Take the inverse SIN of Average
DEG = WorksheetFunction.Degrees(INVSIN)
' Convert back to degrees from Radians
ROUNDED = WorksheetFunction.MROUND(DEG, 5)
' Round to nearest five degrees
End Function

Any help would be greatly appreciated. :

 

[Gary''s Student]

You should DIM your variables, especially cellrange and AvgWndDir. In the
end you need to set AvgWndDir equal to somthing in the function to return a
vlaue. You are off to a good start.

 

[Ron Rosenfeld]

I am attempting to create a custom function using other functions in
EXCEL. I am attempting to take an array of data (range of cells)
perform a few calculations and return the results. The function will be
stored in the appropriate workbook.

Here is the Code

Function AvgWndDir(cellrange)
Radianz = WorksheetFunction.Radians(cellrange)
' Convert from degrees to Radians
SINZ = WorksheetFunction.Sin(Radianz)
' Take the SIN of the Radians
AVGS = WorksheetFunction.Average(SINZ)
'Average the Sines
INVSIN = WorksheetFunction.Asin(AVGS)
' Take the inverse SIN of Average
DEG = WorksheetFunction.Degrees(INVSIN)
' Convert back to degrees from Radians
ROUNDED = WorksheetFunction.MROUND(DEG, 5)
' Round to nearest five degrees
End Function

Any help would be greatly appreciated.

I don't understand your algorithm. But maybe you are doing something different
than what I think.

It seems that you are averaging the sines of the wind direction angles, and
then taking the inverse sine to compute the resultant average wind direction.
I presume your wind directions are measured at some regular time interval.

If that is the case, it would seem to fail under multiple scenarios. In
addition, different angles will have the same sine.

For example, assume the wind blows from 90° half the time, and 180° half the
time.

The Sin of 90° is 1; the sine of 180° is 0; the average of the sines would be
0.5 and the inverse sine of 0.5 is 30° or 150°. But under these circumstances,
wouldn't the average wind direction be 1/2 way between or 135°??

Also, what is the average wind direction if it blows from the East 1/2 the
time, and from the West 1/2 the time? Is it North? South? or meaningless?

If I understand your algorithm, it seems easy enough to implement in VBA as a
UDF. But I don't believe you can count on it to give you an average wind
direction.

=============================
Option Explicit

Function AvgWndDir(cellrange As Range) As Double
Dim c As Range
Dim Radianz As Double
Dim SINZ As Double
Dim AVGS As Double
Dim INVSIN As Double
Dim DEG As Double

For Each c In cellrange
Radianz = Application.WorksheetFunction.Radians(c.Value)
SINZ = SINZ + Sin(Radianz)
Next c

'Average the Sines
AVGS = SINZ / cellrange.Count

'compute inverse sine
INVSIN = WorksheetFunction.Asin(AVGS)

' Convert back to degrees from Radians
DEG = WorksheetFunction.Degrees(INVSIN)

' Round result to nearest five degrees
AvgWndDir = Round(DEG / 5, 0) * 5

'or, for arithmetic rounding use
'AvgWndDir = application.worksheetfunction.Round(DEG / 5, 0) * 5


End Function
==============================


--ron

 

[Alan]

"rexmorgan" <[email protected]>
wrote in message

I am attempting to create a custom function using other functions in
EXCEL. I am attempting to take an array of data (range of cells)
perform a few calculations and return the results. The function will
be stored in the appropriate workbook.

{Snipped code for AverageWindDirection function}

Any help would be greatly appreciated.

Hi Rex,

One question:

If the wind blows from due North 50% of the time, and due South 50% of
the time, then what is the average wind direction?

Alan.


--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address