Excel rotate radar chart

Question

I have been trying to create a windrose that displays the occurence of multiple wind speeds and their respective wind direction. Using other very helpful posts on here I've gotten pretty close to what I want. There is just one thing I can't seem to fix.

As you can see in the figure below the graph starts at 0 degrees while I want the "North" wind direction to start at -11,25 (or +348,75) degrees.

Currently the radial axis labels are added using a pie chart while the rest of the data is plotted in a filled radar chart. It is easy to rotate the pie chart but I can't seem to find a similar function for rotating the radar chart. Any help would be much appreciated. The excel file is attached beneath the figure.

EDIT: Locked excel file against editing

Excel file

Answer 1

Rotating radar charts in Excel can be achieved by building a separate table for plotting the chart. It would have three columns:

Column A: New categories

Column B: Original categories (calculated from A)

Column C: Original data using VLOOKUP() on B

The chart will be plotted using columns B and C. Column B category numbers are offset by the desired number of categories.

If the chart needs to be rotated by other than multiples of a category degree (e.g., 30 degrees for 12 categories), you would need to add rows in between (corresponding to the amount of rotation in relation to the category degree). For example, to rotate a 12-category radar chart by multiples of 15 degrees, one extra row is needed in-between each original category row (to create 24 new categories). In this case, you would need to calculate the intermediate values by linearly interpolating between actual data points.

The trick is that blank category values are not displayed on the chart and the values for these categories blend in smoothly with the real data (because they are interpolated).

I will post an example if the above is not clear enough.

P.S. I cannot look at your new Excel file (in Answers) because it exceeds 5 MB (see screenshot 1).

Answer 2

So I did keep working on this problem and the best solution I've come up with (while using Microsoft Excel) looks as follows:

Currently, the number of sectors in the plot is fixed at 16. If I want to make this number variable, the table required for the plot data requires a very large amount of lookup functions which make the spreadsheet too slow to work with.

I've uploaded the new Excel file here to take a look at: Excel file

Answer 3

I haven't fully digested the netiquette of this website and not sure if it is a good idea to try giving you an answer 6+ months after you posted. Also hope that by this time you found an answer.

If not, this link should be of help: https://superuser.com/questions/687036/how-to-make-a-pie-radar-chart

In the example the creator made one field for each degree and started the first series, which would be equivalent to your north at 0°. However nothing prevents you from starting at 348. I have not tested but I also think that nothing prevents you from adding even more "resolution", e.g. half-degree steps.. or even more to your discretion.

EDIT: following L.Guthardt's feedback. In order to provide you an answer I opted to simplify your table and chart. Mostly for convenience, but also because I struggle to get a full understanding of the original "architecture". Still, the solution should work at any level and is based on two key elements:

first you will have to double the number of rows from 16 to 32 (thus each quadrant being repeated two times, e.g. ... nne - nne - ne - ne...)

second, you have to start and finish with N as showcased here

Direction   Cat6            

N       6           
NNE     4   4       
NNE         6       
NE          4   4   
NE              6   
ENE             4   4
ENE                 6
E       4           4
E       6           
ESE     4   4       
ESE         6       
SE          4   4   
SE              6   
SSE             4   4
SSE                 6
S       4           4
S       6           
SSW     4   4       
SSW         6       
SW          4   4   
SW              6   
WSW             4   4
WSW                 6
W       4           4
W       6           
WNW     4   4       
WNW         6       
NW          4   4   
NW              6   
NNW             4   4
NNW                 6
N       4           4

which will generate

for the pie chart I used a separate range with alternate gaps in the labels

Direction   Dummy
N   1
    1
NNE 1
    1
NE  1
    1
ENE 1
    1
E   1
    1
ESE 1
    1
SE  1
    1
SSE 1
    1
S   1
    1
SSW 1
    1
SW  1
    1
WSW 1
    1
W   1
    1
WNW 1
    1
NW  1
    1
NNW 1
    1