Multiple doughnut chart with fixed sizes

Question

Hi everyone, I'm encountering some difficulties while trying to achieve a specific goal. I'm aiming to create a multiple doughnut chart with a fixed size. My scenario involves 9 variables and 6 group members. Essentially, I want to generate a chart where the circle is divided into the number of variables (resulting in 9 slices) and also divided along the radial axis by the number of members (yielding 6 sections in this case).

Initially, this task seems quite straightforward. However, I'm facing an issue when attempting to input the data. Specifically, I have binary data, and I would like to visualize it by painting the corresponding space in the chart if a member has a value of 1 for a specific variable. Otherwise, the space should remain blank.

Can anyone offer guidance on how to tackle this challenge? I would greatly appreciate any assistance or suggestions. Thank you!

So far I've tried to do it with a doughnut plot, the problem is that on the values of 0, the goughnut plot just ignores the value and divides the circle into 5.

Ideally I would have something like this: where here the variables will be 8 and the members will be 3.

ANd I am achieving:

Whenever I change the values:

The data is below,

Number of members	6
Member <br>Belbin Role	1	2	3	4	5	6	Total
TeamWorker (TW)	0	1	1	1	0	1	4
Co-ordinator (CO)	1	1	0	1	1	1	5
Sharper (SH)	1	1	1	1	1	1	6
Completer Finisher (CF)	1	0	0	0	1	1	3
Implementer (IMP)	1	1	1	0	1	1	5
Specialist (SP)	1	0	1	0	1	0	3
Monitor Evaluator (ME)	1	1	0	1	1	1	5
Plant (PL)	1	1	0	1	1	1	5
Resource Investigator (RI)	1	1	1	1	1	1	6

Answer 1

There is a trick to accomplish this, I've created a sample with random formulas:

Each segment has to be the same size around the circle, therefore we need to get the largest number as segment size.

K2: =MAX(B5:G13)

The next step is to create a data area for the chart so that the 1st item is the original data and the next item is the difference to the Size of a segment. Then the 2nd item from the original data and the difference to the size of the segment. And so on.

If we overwrite the original data with 1 each and set the size to 3 and create a chart from our data area it looks like this:

That means the smaller segments shows the original data and the larger segments shows the difference to the size.

All you have to do it to format each point in a way so it look like your original charts...

It's a bit cumbersome, so I left a macro in the file that does a similar formatting. I'll leave the fine tuning to you.

https://www.dropbox.com/s/abmof1wrrzkezpy/c0e1adec-2e07-485b-a9a6-c9aff27adeb8.xlsm?dl=1

Andreas.