Wind Rose

I don't know how to do it. However, unless you provide representative smaple data in an Excel sheet, I doubt if anyone will attempt to help you.


Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Also... did you post the same Q on another forum? I am fairly sure I saw the same example... somewhere... yesterday.

If so, please re-read the forum rule about cross-posting and include link(s) to the other fora.

There's one guy here who (in particular) is a charts wizard. If you're lucky he'll be around today...

From windroseexcel's website,

Originally Posted by windroseexcel

We use inbuilt VBA coding to sort raw wind speed data into a dynamic frequency table. Next, further VBA is used to form the spokes with form the wind rose diagrams.

there are two main steps to creating this diagram:
1) summarize the raw wind speed data (I would expect that this is mostly COUNTIFS() functions).
2) Build a filled radar chart with multiple data series for each "band" of wind speeds. Directions (including several "dummy" points) will be the categories, and "fraction of data" will be the values (including 0 values for the "dummy" points).

Your sample file only gives raw data. My question for you at this point is what part do you need help with? Can you summarize the data? Are you familiar with radar plots and filled radar charts? What specifically do you need help with?

Your data would suggest the majority of the data points would be plotted in top left quadrant, rather than nicely fanned out like your image.

The attached is just a polar plot of your data.
You could add more series and a formula to colour group the dots.

If you are unfamiliar with the COUNTIFS() function, then I would suggest you start here https://support.office.com/en-us/art...c-aa8c2a866842 and with the many other examples around the internet of this function.

To get the count of a specified wind direction, I would expect something that "count how many entries are greater than a specified minimum bearing and less than a specified maximum bearing and less than a specified wind speed". Should look something like =COUNTIFS($B$2:$B$500,">"&leftbearing,$B$2:$B$500,"<"&rightbearing,$A$2:$A$500,"<="&windspeed). This is a little more complicated for "north", because the bearings reset to 0 at north, so this becomes "bearing greater than minimum bearing or less than maximum bearing". Or logic in a COUNTIFS() is usually the sum of two countifs -- =COUNTIFS($B$2:$B$500,">"&leftbearing,$A$2:$A$500,"<="&windspeed)+COUNTIFS($B$2:$B$500,"<"&rightbearing,$A$2:$A$500,"<="&windspeed). When I set this up in the spreadsheet I:
1) In D2:D5, I entered my desired min bearings (315,45,135,225). Column E was the desired max bearings (45,135,225,315). In column F, I entered "N, E, S, W" (I was only doing the four cardinal directions -- you will probably want to subdivide by more than 4). In G1:J1, I put my wind speed bins (10,5,3,1).
2) In G2, I entered my "north" COUNTIFS() function: =COUNTIFS($B$2:$B$500,">"&$D2,$A$2:$A$500,"<="&G$1)+COUNTIFS($B$2:$B$500,"<"&$E2,$A$2:$A$500,"<="&G$1). Note the mix of relative and absolute references for easy copying. Then I copied G2 and pasted into G2:J2.
3) In G3, I entered the "other directions" COUNTIFS() function: =COUNTIFS($B$2:$B$500,">"&$D3,$B$2:$B$500,"<"&$E3,$A$2:$A$500,"<="&G$1). Copy and paste into G3:J5
4) If, as indicated by your example, you want the fraction of the total number of readings for each bin rather than the absolute count, add a cell with a COUNT() function and divide the above formulas by that reference. I put it in D2 =COUNT($A$2:$A$500), then added /$D$2 to the above formulas.

That should take care of the summary of the data. Creating the wind rose should be a matter of arranging that data in a suitable table and inserting a filled area radar chart. I will use the above table as a lookup table to fill the chart table. If you are unfamiliar with Excel's lookup functions, then search the help files for "lookup and reference functions".

5) I need 3 "spokes" in the radar chart for each direction, plus a blank spoke between directions. I'm doing four directions so that makes for 16 points in the chart. I enter the "category" values in F7:F22. These are "N,N,blank,E,E,E,blank,...,N".
6) I enter my lookup function in G7. I used =IFERROR(INDEX(G$2:G$5,MATCH($F7,$F$2:$F$5,0)),0). The INDEX(...,MATCH(...)) part of the function finds the appropriate value from the lookup table. The IFERROR() part of the function returns 0 when a match is not found (there is no blank row in the lookup table, which causes an error). Copy G7 and paste into G7:J22
7) Now the hard part is done. Make a copy of G1:J1 and paste into G6:J6. Then select F6:J22 -> Insert -> Radar chart -> Filled area radar.
8) Format the chart as desired (repair any mistakes made along the way).

As AndyPope noted, the chart shows that essentially all entries are north or west. You should be able to easily expand that to a more finely divided grid of direction and wind speed.

Haven't been able to get on the forum for a while.

I cannot readily tell what you have done wrong just from the pictures. If I had to guess, it looks like the division by total somehow only applied to the West data, and did not get applied to the North data. How did you apply the division to the North counts? Did you recognize that you need to add parentheses around the two countifs() so both counts are performed before the division?

So, following what Mr Shorty said above, where you have multiple COUNTIFS you need to bracket them together before dividing. So, in G2 copied across:

=(COUNTIFS($B$2:$B$500,">"&$D2,$A$2:$A$500,"<="&G$1)+COUNTIFS($B$2:$B$500,"<"&$E2,$A$2:$A$500,"<="&G$1))/$C$2

Without the brackets, only the last of the COUNTIFS is divided by C2.

Hope this helps.

- Is there a way to put a calculations box in the chart to calculate and show mean speed etc?

Yes -- kind of. Does the calculation have to occur in the text box? I think this https://support.office.com/en-us/art...2-b10c62fca6f5 is by far the easiest way, but this means that the calculation (including any text concatenation) takes place in a cell (or over several cells, as needed) in the spreadsheet, and the result is linked into the text box -- so the text box itself doesn't actually do the calculations. Would that be allowed, or do you need the text box to perform the calculation?

- Removing radial lines to 1 every 22.5degrees

Recognizing that the "radial lines"/spokes of a radar chart are part of the "horizontal" category axis (so the axis has no numeric sense of angle), this is controlled by the "interval between tick marks" setting from the "format axis" dialog for the category axis. Unfortunately, my copy of Excel (2007) would not let me access the formatting parameters of the category axis in a radar chart. I changed the chart type to one of the "rectangular" chart types that allows ready access to the category axis (a filled area chart -- though I expect any of the other category axis chart types would do just as well) -> Select the category axis -> Format axis -> change "interval between tick marks" to a suitable value (I think 4 is what you want, but experiment until you get the right value) -> Change chart type back to Filled area radar chart. Of course, if your version of Excel will allow you to access the category axis properties directly as a radar chart, then simply adjust that property from the format axis dialog.

- Reducing direction labels to 1 every distinct heading

You should be able to see from the existing chart that, for every cell in the category axis range (F19:F82), a corresponding axis label is shown on the chart. Changing the number of direction labels means changing the values in F19:F82 so that there is only one text label for each direction in that range. That means adjustments to the lookup formulas so that you still have the correct arrangement of values in G19:R82. This should be as simple as clearing the desired cells in F19:F82 and adjusting the corresponding lookup formulas. Let us know if you need help with those edits.

If your version/copy of Excel does something different than mine with the axis settings, I am not sure what to recommend. I suppose you could try something in VBA (using the TickMarkSpacing property of the axis object https://msdn.microsoft.com/en-us/vba...property-excel ) and see if that allows you to change the format of the category axis. Something as simple as this may work:

Please Login or Register  to view this content.

I might be able to help with those calculations. Per cent calm seems like it should just be another COUNTIFS()/total count function =COUNTIFS(entries in column A that are less than whatever calm means)/total count. Is there something specific about that COUNTIFS() function you are having trouble piecing together?
Because wind velocity is a vector quantity (both a speed and a direction component), I know there are special considerations when computing different statistics (like average velocity, peak velocity, etc.) -- and even different ways of doing it depending on exactly what you want "peak wind speed and direction" to mean. If you can help us understand the math behind how your discipline likes to calculate these quantities, I'm sure we can help you translate those into Excel formulas. If you are unsure how people in your discipline like to calculate these quantities, then I suggest you inquire around among those familiar with your discipline about how these quantities are to be calculated. Once you have that information, then you can bring it back here and we can help you translate those calculations into Excel formulas.

The formula in column T outputs non-space text for the entry corresponding to the max value in S2:S17, so I would probably just use a lookup function in W4 based on the max value in S2:S17. Maybe something like =VLOOKUP(MAX(S2:S17),S2:T17,2,FALSE). I do not see anything in this to handle the scenario where there are two max values in S2:S17, so note that, if the max value in S2:S17 ever occurs twice, then this lookup function will only capture the first max.