[wxqc] Text to Degrees

[Steve Dimse]

On Apr 9, 2007, at 12:47 AM, Shell Shrader wrote:

> I initially just averaged the data per month and per year. Out of  
> curiosity, for N, I substituted 360 for 0 and got completely  
> different (not surprisingly now that I think about it) monthly and  
> yearly "average wind directions." This told me I was not doing  
> something right but I thought there must be a way that historical  
> predominant wind direction is determined.

This is the 360 degree problem mentioned in two prior posts with  
algorithms to address it. To visualize it, imagine two readings of  
359 degrees and 1 degree. The mathematical average is 180, but that  
clearly is not the correct answer, it is zero (or 360) degrees. This  
is not simply a problem for readings at zero, but for any values  
around the compass you hope to average.

You must solve this by using vector math, either of the two given  
algorithms work. There is no simple (add x or divide by y) solution  
to this problem. I do not use excel for math, but it may well have  
vector functions that would simply implementing one of these algorithms.

You also have to decide if magnitude matters. Do you want one hour of  
east wind to balance one hour of west wind regardless of magnitudes,  
or do you want three hours of east wind at 3 MPH to balance 1 hour of  
west wind at 9 MPH? Your examples don't show magnitudes, but it could  
be essential, some weather stations output 0 degrees when winds are  
calm, others output the last value with a non-zero magnitude. Either  
way, if you do not remove values with zero magnitudes they can skew  
your results (fatally if you have a zero degree type of station).  
Some stations (particularly the old Peet anemometers) need a few MPH  
of wind before they return a valid reading, so magnitudes are  
critical for those as well.

Can-o-worms you opened!

> Assuming that each year's spreadsheet has about 35000 rows of  
> readings, I'm hoping to not have to do a lot of actual manipulation  
> of the data. I could always sort the data by "text" wind direction  
> without converting it, then sort it by date and count how many rows  
> per month and per year there is for each direction but I was hoping  
> there would be a less labor-intensive way.

I think excel has a count function, doesn't it? You should not need  
to sort or convert to numeric degrees.

Or, you could just call the National Weather Service and see if they  
can give you this info ;-)

Steve K4HG