Charting Textual Statistics

[TheDevil]

Hi all,

i've got this worksheet going. the data is a collection of stats from
market research. its got name, company, address questions and answers
some are simple answers like yes no, others are multiple choices.

so basically i want to draw up charts for this. but i figure i can onl
do this with numerical data not textual data. is there a formula or wa
around it?

i am absolutely clueless how to do it.

so if it works out i want a pie chart or bar chart that represents th
question and the percentages of how the answers were received. e.g. fo
a sample question of what kind of pets do you have... i want a chart t
represent the one question "how many pets do you have" and the data i
the chart to reflect the answers collected...

soooo... if 5 answered dog, and 7 answered cats. i want the chart t
show this as if its drawn up a chart from numerical data to begin with
see below for further detail...

--------------------------------

Question: what is your fav animal?
dog
cat
dog
dog
dog
cat
cat
monkey
elephant
...
..
..

i want the above data to be graphed into a nice pie or bar char
representing the percentages shown proportionally...

IS THIS EVEN POSSIBLE???

 

[Andy Pope]

[This is a reply I gave to a very similar question the other day.
Just replace Apple/Orange with Cat/Dog]

Hi,

Excel will not automatically summarize you textual values.
For example if your data, in A1:A6, was;

Apple
Orange
Orange
Orange
Apple
Apple

you would not get a 2 slice pie chart with each slice being 50%. You
need to summarize the data using formula or pivot table. The create the
pie chart on the summary data.

Here is an example of summarizing the example data.
B1: ="Apple" C1: =COUNTIF($A$1:$A$6,B1)
B2: ="Orange" C2: =COUNTIF($A$1:$A$6,B2)

The select B1:C2 in order to create your chart

Cheers
Andy

 

[TheDevil]

Wow!

Excellent! Thanks for that!

Cheers

TheDevi

 

[Lon]

Yes, excellent indeed. Thanks.

A follow-up question, if I may: Why do you use absolute locations ($A$1) in
your forumlas, rather than plain cell locations? The result, in this example
at least, is the same.

-Lon

[This is a reply I gave to a very similar question the other day.
Just replace Apple/Orange with Cat/Dog]

Hi,

Excel will not automatically summarize you textual values.
For example if your data, in A1:A6, was;

Apple
Orange
Orange
Orange
Apple
Apple

you would not get a 2 slice pie chart with each slice being 50%. You need
to summarize the data using formula or pivot table. The create the pie
chart on the summary data.

Here is an example of summarizing the example data.
B1: ="Apple" C1: =COUNTIF($A$1:$A$6,B1)
B2: ="Orange" C2: =COUNTIF($A$1:$A$6,B2)

The select B1:C2 in order to create your chart

Cheers
Andy

Hi all,

i've got this worksheet going. the data is a collection of stats from a
market research. its got name, company, address questions and answers.
some are simple answers like yes no, others are multiple choices.

so basically i want to draw up charts for this. but i figure i can only
do this with numerical data not textual data. is there a formula or way
around it?

i am absolutely clueless how to do it. so if it works out i want a pie
chart or bar chart that represents the
question and the percentages of how the answers were received. e.g. for
a sample question of what kind of pets do you have... i want a chart to
represent the one question "how many pets do you have" and the data in
the chart to reflect the answers collected... soooo... if 5 answered dog,
and 7 answered cats. i want the chart to
show this as if its drawn up a chart from numerical data to begin with.
see below for further detail...

--------------------------------

Question: what is your fav animal?
dog
cat
dog
dog
dog
cat
cat
monkey
elephant
..
.
.

i want the above data to be graphed into a nice pie or bar chart
representing the percentages shown proportionally... IS THIS EVEN
POSSIBLE???
---------------------------------------------------------

cheers.

 

[Andy Pope]

The combination of absolute and relative references allows me to type
the formula in C1 and fill down automatically. The reference to the
table of data A1:A6 will remain fixed whilst the reference to the lookup
value in B1:B2 will automatically update.

Cheers
Andy

Yes, excellent indeed. Thanks.

A follow-up question, if I may: Why do you use absolute locations ($A$1) in
your forumlas, rather than plain cell locations? The result, in this example
at least, is the same.

-Lon

[This is a reply I gave to a very similar question the other day.
Just replace Apple/Orange with Cat/Dog]

Hi,

Excel will not automatically summarize you textual values.
For example if your data, in A1:A6, was;

Apple
Orange
Orange
Orange
Apple
Apple

you would not get a 2 slice pie chart with each slice being 50%. You need
to summarize the data using formula or pivot table. The create the pie
chart on the summary data.

Here is an example of summarizing the example data.
B1: ="Apple" C1: =COUNTIF($A$1:$A$6,B1)
B2: ="Orange" C2: =COUNTIF($A$1:$A$6,B2)

The select B1:C2 in order to create your chart

Cheers
Andy

Hi all,

i've got this worksheet going. the data is a collection of stats from a
market research. its got name, company, address questions and answers.
some are simple answers like yes no, others are multiple choices.

so basically i want to draw up charts for this. but i figure i can only
do this with numerical data not textual data. is there a formula or way
around it?

i am absolutely clueless how to do it. so if it works out i want a pie
chart or bar chart that represents the
question and the percentages of how the answers were received. e.g. for
a sample question of what kind of pets do you have... i want a chart to
represent the one question "how many pets do you have" and the data in
the chart to reflect the answers collected... soooo... if 5 answered dog,
and 7 answered cats. i want the chart to
show this as if its drawn up a chart from numerical data to begin with.
see below for further detail...

--------------------------------

Question: what is your fav animal?
dog
cat
dog
dog
dog
cat
cat
monkey
elephant
..
.
.

i want the above data to be graphed into a nice pie or bar chart
representing the percentages shown proportionally... IS THIS EVEN
POSSIBLE???
---------------------------------------------------------

cheers.

 

[Lon]

Aah. Very good. The formula looks more difficult type, and it is, at first.
But by taking the time to do so once, it only has to be typed once. Clever.

Thanks,

-Lon

The combination of absolute and relative references allows me to type the
formula in C1 and fill down automatically. The reference to the table of
data A1:A6 will remain fixed whilst the reference to the lookup value in
B1:B2 will automatically update.

Cheers
Andy

Yes, excellent indeed. Thanks.

A follow-up question, if I may: Why do you use absolute locations ($A$1)
in your forumlas, rather than plain cell locations? The result, in this
example at least, is the same.

-Lon

[This is a reply I gave to a very similar question the other day.
Just replace Apple/Orange with Cat/Dog]

Hi,

Excel will not automatically summarize you textual values.
For example if your data, in A1:A6, was;

Apple
Orange
Orange
Orange
Apple
Apple

you would not get a 2 slice pie chart with each slice being 50%. You need
to summarize the data using formula or pivot table. The create the pie
chart on the summary data.

Here is an example of summarizing the example data.
B1: ="Apple" C1: =COUNTIF($A$1:$A$6,B1)
B2: ="Orange" C2: =COUNTIF($A$1:$A$6,B2)

The select B1:C2 in order to create your chart

Cheers
Andy

TheDevil wrote:

Hi all,

i've got this worksheet going. the data is a collection of stats from a
market research. its got name, company, address questions and answers.
some are simple answers like yes no, others are multiple choices.

so basically i want to draw up charts for this. but i figure i can only
do this with numerical data not textual data. is there a formula or way
around it?

i am absolutely clueless how to do it. so if it works out i want a pie
chart or bar chart that represents the
question and the percentages of how the answers were received. e.g. for
a sample question of what kind of pets do you have... i want a chart to
represent the one question "how many pets do you have" and the data in
the chart to reflect the answers collected... soooo... if 5 answered
dog, and 7 answered cats. i want the chart to
show this as if its drawn up a chart from numerical data to begin with.
see below for further detail...

--------------------------------

Question: what is your fav animal?
dog
cat
dog
dog
dog
cat
cat
monkey
elephant
..
.
.

i want the above data to be graphed into a nice pie or bar chart
representing the percentages shown proportionally... IS THIS EVEN
POSSIBLE???
---------------------------------------------------------

cheers.