+ Reply to Thread
Results 1 to 18 of 18

Averaging Wind Directions

  1. #1
    Registered User
    Join Date
    08-18-2012
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    19

    Averaging Wind Directions

    So following on from a post over the weekend when I got some fantastic help with some data I am using for my dissertation, I was wondering if anyone had any ideas how to solve this one...

    I've got a list of days and times during a year, and with each of these I have values for the wind direction. I need a list of daily mean wind directions to go alongside the column (in sheet 2) of days of the year.

    This is obviously complicated by the fact that excel wont understand that the average of say, 10 and 350 is 0 (not 180).

    Does anyone have any idea how I could get what I'm after?

    (I've attached the file to show the data that I'm working with)

    Thanks in advance for any help

    Andy
    Attached Files Attached Files

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Averaging Wind Directions

    The topic is non-trivial. Please review this first:

    http://www.mrexcel.com/forum/excel-q...-headings.html
    Gary's Student

  3. #3
    Registered User
    Join Date
    08-18-2012
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Averaging Wind Directions

    I've read the thread, but the useful post at the end only seems to work when there are two angles (wind directions in my case) which are to be averaged. Unless I'm missing something, this doesn't work for me?

    Andy

  4. #4
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Averaging Wind Directions

    Even two angles are non-trivial. If the first angle is 20 degrees and the second angle is 200 degrees, what should the "average" be?

  5. #5
    Registered User
    Join Date
    08-18-2012
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Averaging Wind Directions

    well for the wind direction case, the average would be 110? Is it not possible to get excel to work this out for me?

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Averaging Wind Directions

    Please Login or Register  to view this content.
    It's not too meaningful to average direction without also considering speed.
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Averaging Wind Directions

    It's not too meaningful to average direction without also considering speed.
    I was thinking the same thing. Do meteorologists treat wind as a vector when they average it the way physics students average velocities? Of course, this means wind speed and direction are averaged together and not independently.

    Andy, as a PhD/MS student, you are probably our resident expert in this subject, and the question at this point seems more about the math/meteorology of wind speed rather than an excel questioin. How do meteorologist average wind speed/direction?

  8. #8
    Registered User
    Join Date
    08-18-2012
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Averaging Wind Directions

    If I'm honest, I'm not sure how useful its going to be as a variable. Its going into a stepwise multiple regression model, so if it turns out not to be a useful predictor variable, it can just get disregarded anyway.

    I'm only a lowly undergrad student, so I only have limited experience in the subject. I'm not sure how this is normally done, and most of my lecturers are all away for the summer at the moment. One of the studies I have looked at from the Journal of Geophysical Research does use "Mean Wind Direction, as a predictor variable but offers no insight into how they calculated it.

    I have found this formula from a different thread, which I'll probably make use of as it seems to work:


    =MOD(DEGREES(ATAN2(AVERAGE(COS(RADIANS(A2:A3))),AVERAGE(SIN(RADIANS(A2:A3)))))+360,
    360)


    Andy

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Averaging Wind Directions

    From http://www.ndbc.noaa.gov/wndav.shtml

    Two wind averaging methods are used to calculate average wind speed and direction.

    1.The first wind averaging technique applies to those measurements reported by all DACT, VEEP, and ARES payloads. The average wind speed is the simple scalar average of the wind speed observations. A "unit-vector" average is used to calculate the average wind direction. In this technique, unity serves as the length of the vector, and the wind direction observations serve as the orientation of the vector. The u and v components are then calculated for each observation. Next, the average u and v components are computed and the average wind direction is derived from "arctan(u/v)." Note that this technique will produce greater wind speeds than if a true vector average was used.

    2.The second method, used to calculate average speed and direction reported by NDBC's older GSBP payloads, is a true vector average. In this scheme, the magnitude of the vector is represented by the wind speed observation and the direction observations are used for the orientation. The vectors are then broken down into their u and v components. All u and v components are then averaged separately. The resulting average speed and direction are calculated from the Pythagorean Theorem and "arctan(u/v)," respectively.
    Those both seem peculiar to me. The second seems reasonable as to direction, but the average speed should be RMS. If winds come in squall force equally from east and west over time, the average direction may be zero, but not the average speed.

    EDIT: That formula is the same as mine, just doing the averaging in a single formula.

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Averaging Wind Directions

    Did you see shg's solution above? He used the ATAN2 formula. The problem with your spreadsheet is that your original dates are listed as text (with an extra space at the beginning). You need to fix that first and then you can use AVERAGEIF instead of AVERAGE. I did it slightly different (calculating all the COS and SIN first and then averaging). Here's my spreadsheet.
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  11. #11
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Averaging Wind Directions

    Even ignoring wind speed there's still an issue with whether the average direction makes any sense. When all the directions are similar an average may be valid but if you average 90, 180, 270 and 360 what would you expect?

    Having said that I used this "array formula" in B2 to get the average for the date in A2

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER and copied down. That's just a complex version of the formula shg suggested, tweaked to pull out the relevant data only

    see attached
    Attached Files Attached Files
    Audere est facere

  12. #12
    Registered User
    Join Date
    08-18-2012
    Location
    Sheffield, UK
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Averaging Wind Directions

    Wow, again I'm overwhelmed by the helpful responses on this forum

    DaddyLongLegs, I'm using that formula with the same data, just to see if I can reproduce the outcome, but It doesn't seem to work.

    Is there something in this file that I'm doing "obviously" wrong?

    Thanks in advance (again)

    Andy
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-14-2013
    Location
    Portugal
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Averaging Wind Directions

    If wind speed > 0 (in km/h) then calculate average of wind directions.

    How? Because 'averageif' may not allow 'cos', 'radians', 'sum'... (but yes for 'average').
    Last edited by nunito; 03-15-2013 at 08:22 PM.

  14. #14
    Registered User
    Join Date
    03-14-2013
    Location
    Portugal
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Averaging Wind Directions

    Please response my previous post.

  15. #15
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Averaging Wind Directions

    I hate to add complications to this but I ran into this problem when laying out runways for a Radio Control club. After beating my head against the wall to figure out the best layout for the flying of these expensive models, I contacted Environment Canada and they told me that my "averaging" of the wind directions was only part of the "equation" as the wind velocity had to be taken into account, the duration of the wind direction and the time of year. In the end they gave me two headings for the most prevalent wind directions for daytime operations. It worked out quite well.

    If you contact the environment people in the UK, perhaps they can advise you on how to make the calculations the way that they do it.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  16. #16
    Registered User
    Join Date
    03-14-2013
    Location
    Portugal
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Averaging Wind Directions

    PLEASE response to my post #13!!!

  17. #17
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Averaging Wind Directions

    nunito: This forum is pretty strict about not posting your question in someone else's (old) thread. Start a new thread, and, if you feel this thread is particularly relevant, include a link to this thread.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  18. #18
    Registered User
    Join Date
    03-14-2013
    Location
    Portugal
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Averaging Wind Directions

    Quote Originally Posted by MrShorty View Post
    nunito: This forum is pretty strict about not posting your question in someone else's (old) thread. Start a new thread, and, if you feel this thread is particularly relevant, include a link to this thread.
    Ok sorry but I have added the thread "Average of directions wind".

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1