How to create a wind rose from wind speed and direction data in excel

Have a look on attached file (left only one month data to keep file small, but included now wind (empty C readings) to show how it works in such cases.

For instance lets set direction bins 15 deg wide.

In column D helper formula:

Please Login or Register  to view this content.

copied down
it converts anything from 352.5,360 and from 0,7.5 to less than 0
7.5,22.5 to less than 15
22.5,37.5 to less than 30
etc.

now list somewhere
0
15
30
....
345
then in next column one can use frequency (note - it is array = ctrl+shift+enter formula over the whole selection G2:G25)

and then one can make a graph - I used radar type.

PS. I divided FREQUENCY result by 24 (hours) and by 6 (readings per hour) to have total length in days in each direction not just total number of readings in each direction.

Hi gpack,

Thank you for posting a most interesting question. After 5 minutes of research, I am now a 'Wind Rose Tyro'. I cheated and wrote a macro to create formulas that are required. Since I only have access to Excel 2003, the SUMPRODUCT formula had to be used instead of COUNTIFS which is available starting in Excel 2007.

The macro is preset to 45 degrees per position, but that can be changed to anything you want that is evenly divisible into 360. See the attached workbook (in zip format becuase the file was too big) that implements the macro with your data.

Lewis

Please Login or Register  to view this content.

I'd delete this part:

Please Login or Register  to view this content.

because with it in rows 7 and 8 data from columns L-P does not sum up to column J.

Please note that if such table will be used for radar graph the first value (plotted in topmost position) will correspond to all data collected from angles <0 , 44> range - so it would be wiser to have it plotted not vertically, but at 22.5 deg ;-).

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread