Pie of pie chart with 2 data series
 

Hi,

I am trying to process the results from a survey. For a particular question,
the surveyed people can answer with (say) yes, no or partially. They also
can justify their answer, with (say) something like cheaper, simpler or
don't know. I would like to have a pie chart with the yes, no and partially
answers to see how many have responded with each choice, but then also a pie
of pie chart with for the yes answers (for example) the distribution of the
justifications.

Here's what I have done so far (this is just a proof of concept with a very
small data set). The headings yes/no/partially/justification are in B1:E1.
Then in B2:E10, I have the answers. In the B to D column, there is a 1 when
this was the chosen answer (and blank for the other two) and in the E
column, there is the actual justification (cheaper/simpler/don't know). I
have summed up the responses in B11:D11.

Then to count how many have justified their yes answer by simpler, cheaper
or don't know, I am using DCOUNTA. So I have copied the headings in B13:E13,
B16:E16 and B19:E19. B14, B17 and B20 are all equal to one, while E14 is
"simpler", E17 is "cheaper" and E20 is "don't know". Finally, I have the
following formulae in F13, F16 and F19:
F13: =DCOUNTA($B$1:$E$10,$B$1,B13:E14)
F16: =DCOUNTA($B$1:$E$10,$B$1,B16:E17)
F19: =DCOUNTA($B$1:$E$10,$B$1,B19:E20)

and that gives me the right answers, i.e. how many people have responded yes
because it was cheaper, how many have responded yes because it was simpler,
etc... My problem now is to create the pie of pie chart. The chart template
in Excel doesn't allow you to have a different data set for the sub-pie
chart. Does anybody know how to do this or can maybe suggest a better way of
doing this? I hope I was clear enough about what I was trying to do. I can
send you the actual Excel spreadsheet if that makes it any easier.

Many thanks in advance,

Arnaud

Pie of pie chart with 2 data series
 

Hi,

See Jon's article for more information on pie of pie
http://pubs.logicalexpressions.com/P...cle.asp?ID=471

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Arnaud Miege" wrote in message
...
Hi,

I am trying to process the results from a survey. For a particular
question, the surveyed people can answer with (say) yes, no or partially.
They also can justify their answer, with (say) something like cheaper,
simpler or don't know. I would like to have a pie chart with the yes, no
and partially answers to see how many have responded with each choice, but
then also a pie of pie chart with for the yes answers (for example) the
distribution of the justifications.

Here's what I have done so far (this is just a proof of concept with a
very small data set). The headings yes/no/partially/justification are in
B1:E1. Then in B2:E10, I have the answers. In the B to D column, there is
a 1 when this was the chosen answer (and blank for the other two) and in
the E column, there is the actual justification (cheaper/simpler/don't
know). I have summed up the responses in B11:D11.

Then to count how many have justified their yes answer by simpler, cheaper
or don't know, I am using DCOUNTA. So I have copied the headings in
B13:E13, B16:E16 and B19:E19. B14, B17 and B20 are all equal to one, while
E14 is "simpler", E17 is "cheaper" and E20 is "don't know". Finally, I
have the following formulae in F13, F16 and F19:
F13: =DCOUNTA($B$1:$E$10,$B$1,B13:E14)
F16: =DCOUNTA($B$1:$E$10,$B$1,B16:E17)
F19: =DCOUNTA($B$1:$E$10,$B$1,B19:E20)

and that gives me the right answers, i.e. how many people have responded
yes because it was cheaper, how many have responded yes because it was
simpler, etc... My problem now is to create the pie of pie chart. The
chart template in Excel doesn't allow you to have a different data set for
the sub-pie chart. Does anybody know how to do this or can maybe suggest a
better way of doing this? I hope I was clear enough about what I was
trying to do. I can send you the actual Excel spreadsheet if that makes it
any easier.

Many thanks in advance,

Arnaud

Pie of pie chart with 2 data series
 

Thanks,

I managed to get something working based on the information in the article
you mentioned.

Arnaud

"Andy Pope" wrote in message
...
Hi,

See Jon's article for more information on pie of pie
http://pubs.logicalexpressions.com/P...cle.asp?ID=471

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Arnaud Miege" wrote in message
...
Hi,

I am trying to process the results from a survey. For a particular
question, the surveyed people can answer with (say) yes, no or partially.
They also can justify their answer, with (say) something like cheaper,
simpler or don't know. I would like to have a pie chart with the yes, no
and partially answers to see how many have responded with each choice,
but then also a pie of pie chart with for the yes answers (for example)
the distribution of the justifications.

Here's what I have done so far (this is just a proof of concept with a
very small data set). The headings yes/no/partially/justification are in
B1:E1. Then in B2:E10, I have the answers. In the B to D column, there is
a 1 when this was the chosen answer (and blank for the other two) and in
the E column, there is the actual justification (cheaper/simpler/don't
know). I have summed up the responses in B11:D11.

Then to count how many have justified their yes answer by simpler,
cheaper or don't know, I am using DCOUNTA. So I have copied the headings
in B13:E13, B16:E16 and B19:E19. B14, B17 and B20 are all equal to one,
while E14 is "simpler", E17 is "cheaper" and E20 is "don't know".
Finally, I have the following formulae in F13, F16 and F19:
F13: =DCOUNTA($B$1:$E$10,$B$1,B13:E14)
F16: =DCOUNTA($B$1:$E$10,$B$1,B16:E17)
F19: =DCOUNTA($B$1:$E$10,$B$1,B19:E20)

and that gives me the right answers, i.e. how many people have responded
yes because it was cheaper, how many have responded yes because it was
simpler, etc... My problem now is to create the pie of pie chart. The
chart template in Excel doesn't allow you to have a different data set
for the sub-pie chart. Does anybody know how to do this or can maybe
suggest a better way of doing this? I hope I was clear enough about what
I was trying to do. I can send you the actual Excel spreadsheet if that
makes it any easier.

Many thanks in advance,

Arnaud