Assigning datalabels to doughnut charts with vba

[claude85]

Hi,

I have my data values and also my data labels to create the doughnut chart. Nevertheless, I want to place my labels always near the intersection of two slices of the doughnut to give a kind of limits. Therefore, I have to know how I can access the position of the intersection and position my labels to the wanted position.
I mean if the chart was created and one slice is defined by four points which have own names and is it only that they are distinguished by the angle. But in this way, I think one should also have access to this points via VBA and to be able to place the datalabels near these points.

I hope you can help me to solve my problem.

Thanks in advance

[p45cal]

Supply a file to play with!

[claude85]

Hi,

Here a little test-file. The problem is in the sub "create_graph". I have made a graph and place the labels, as i want but as I want to use the sub more than once and with different dataset, the positioning of the labels should be made automatically. Therefore I need this information about the doughnut graph.

Graph_Tester.xlsm

[p45cal]

I see in your file that you have been doing some superimposing of xy scatter chart on top of your doughnut chart.
So taking that idea forward, you could do the same for your labels.
In the attached, in Feuil1 (2), I have manually superimposed such a chart on the doughnut chart, however all of what I've done can be achieved with code.
Note that the source of my doughnut chart is slightly different from yours; I've used what I think is the raw data, summed it at the bottom (A6) and plotted that directly using a 270 degree rotation for the first slice.
Then I created some x and y values for the scatter chart to be placed on top (H2:I6). (These values are calculated from multiple columns but I have put the equivalent fomula into single cells (K2:L6) - we may use this in the future.)
I then manually adjusted this scatter chart to fit on top of the doughnut. You can see the points line up very well with the limits of your doughnut chart segments. Of course, the points will be invisible. I then added labels; those to the left of centre on the left of the point, those to the right of centre to the right - again, code can handle this.
Capture3.JPG
Is this a route you could envisage following?

[claude85]

Hi,

Thanks, unfortunately from work, I can not download your file, but to put on a XY scatterplot on my doughnut to make the labeling, is possible, I see no problem why I can not handle it in this way. I have to take a look on your file because I do not see which data you have taken because it seems that they are different from mine.
By the way, thanks for the idea, but one question remains, how do you compute the right position of the points to make it fit automatically by code?

[p45cal]

I have to take a look on your file because I do not see which data you have taken because it seems that they are different from mine.

Yes they are different because I wanted to have an example of data where there were intersections of doughnut slices on the right and left of the chart.

By the way, thanks for the idea, but one question remains, how do you compute the right position of the points to make it fit automatically by code?

By code I haven't done yet, but worksheet formula as follows (I can only get a picture to display properly) The formulae in row 2 need to be copied down. The $ symbols in cell E2 are important. Cos(ine) values are the x values, sin(e) are the y values.:
Capture3.JPG





The formulae can be condensed to one cell:
Capture3.JPG

[claude85]

Ok, I understand you compute out of the points the sine and cosine and afterwards with the help of an XY scatterplot you plot them on the doughnut chart as I will do it afterwards also with my second graph which is also I scatterplot.
Nevertheless, as you have taken data which represents the intersection of the doughnut slice, the question still remains, by using arbitrary data, how to get the right data to find these intersection, or get I something wrong?

[p45cal]

The xy scatter is plotted on an x scale of -1 to +1, a y scale of 0 to +1. Always.
Any segment value represents a proportion of the full (half) circle.
I use the cumulative (columnm E), proportion (column F) to calculate x and y (column E for the labels too).

Instead of plotting the actual values in a doughnut chart, you could have calculated the percentage contribution of each segment and plotted that. The segment intersections would be in just the same places.

[p45cal]

An update to the file attached (some simplifications/improvements).
It may help you understand if you change the values in A2:A5 from 40000000, 7000000, 60000000, 60000000
to: 10,20,30,40.
The chart will update itself.

These new values add up to 100 and you will be able to see the values in all the steps of the calculations and it should help you undertstand what's going on.

Capture3.JPG


It may be a good idea to incorporate your own scatterplot into mine as additional series instead of creating a 3rd chart.

[claude85]

ok, I got now your idea, I have change it a little bit so that I do not have to rotate my doughnut 270° but only 90° as wanted. The only problem remains now the computation of the sinus and cosinus by code, but I think that will be feasible.

Thanks a lot to you for this idea with the scatterplot