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 B1111.

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

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" <(E-Mail Removed)> wrote in message
news:%23Cr%(E-Mail Removed)...
> 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 B1111.
>
> 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
>

Thanks,

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

Arnaud

"Andy Pope" <(E-Mail Removed)> wrote in message
news:EACCBC6A-0830-4AAD-A495-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%23Cr%(E-Mail Removed)...
>> 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 B1111.
>>
>> 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
>>
>