A
Arnaud Miege
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
11.
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
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
11.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