software

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 quot;how many pets do you havequot; 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.--
TheDevil
------------------------------------------------------------------------
TheDevil's Profile: www.excelforum.com/member.php...oamp;userid=33708
View this thread: www.excelforum.com/showthread...hreadid=534879[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: =quot;Applequot; C1: =COUNTIF($A$1:$A$6,B1)
B2: =quot;Orangequot; C2: =COUNTIF($A$1:$A$6,B2)

The select B1:C2 in order to create your chart

Cheers
Andy

TheDevil wrote:
gt; Hi all,
gt;
gt; i've got this worksheet going. the data is a collection of stats from a
gt; market research. its got name, company, address questions and answers.
gt; some are simple answers like yes no, others are multiple choices.
gt;
gt; so basically i want to draw up charts for this. but i figure i can only
gt; do this with numerical data not textual data. is there a formula or way
gt; around it?
gt;
gt; i am absolutely clueless how to do it.
gt;
gt; so if it works out i want a pie chart or bar chart that represents the
gt; question and the percentages of how the answers were received. e.g. for
gt; a sample question of what kind of pets do you have... i want a chart to
gt; represent the one question quot;how many pets do you havequot; and the data in
gt; the chart to reflect the answers collected...
gt;
gt; soooo... if 5 answered dog, and 7 answered cats. i want the chart to
gt; show this as if its drawn up a chart from numerical data to begin with.
gt; see below for further detail...
gt;
gt; --------------------------------
gt;
gt; Question: what is your fav animal?
gt; dog
gt; cat
gt; dog
gt; dog
gt; dog
gt; cat
gt; cat
gt; monkey
gt; elephant
gt; ..
gt; .
gt; .
gt;
gt; i want the above data to be graphed into a nice pie or bar chart
gt; representing the percentages shown proportionally...
gt;
gt; IS THIS EVEN POSSIBLE???
gt;
gt; ---------------------------------------------------------
gt;
gt; cheers.
gt;
gt;

--

Andy Pope, Microsoft MVP - Excel
www.andypope.info


Wow!

Excellent! Thanks for that!

Cheers

TheDevil--
TheDevil
------------------------------------------------------------------------
TheDevil's Profile: www.excelforum.com/member.php...oamp;userid=33708
View this thread: www.excelforum.com/showthread...hreadid=534879Yes, 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.

-Lonquot;Andy Popequot; gt; wrote in message
...
gt; [This is a reply I gave to a very similar question the other day.
gt; Just replace Apple/Orange with Cat/Dog]
gt;
gt; Hi,
gt;
gt; Excel will not automatically summarize you textual values.
gt; For example if your data, in A1:A6, was;
gt;
gt; Apple
gt; Orange
gt; Orange
gt; Orange
gt; Apple
gt; Apple
gt;
gt; you would not get a 2 slice pie chart with each slice being 50%. You need
gt; to summarize the data using formula or pivot table. The create the pie
gt; chart on the summary data.
gt;
gt; Here is an example of summarizing the example data.
gt; B1: =quot;Applequot; C1: =COUNTIF($A$1:$A$6,B1)
gt; B2: =quot;Orangequot; C2: =COUNTIF($A$1:$A$6,B2)
gt;
gt; The select B1:C2 in order to create your chart
gt;
gt; Cheers
gt; Andy
gt;
gt; TheDevil wrote:
gt;gt; Hi all,
gt;gt;
gt;gt; i've got this worksheet going. the data is a collection of stats from a
gt;gt; market research. its got name, company, address questions and answers.
gt;gt; some are simple answers like yes no, others are multiple choices.
gt;gt;
gt;gt; so basically i want to draw up charts for this. but i figure i can only
gt;gt; do this with numerical data not textual data. is there a formula or way
gt;gt; around it?
gt;gt;
gt;gt; i am absolutely clueless how to do it. so if it works out i want a pie
gt;gt; chart or bar chart that represents the
gt;gt; question and the percentages of how the answers were received. e.g. for
gt;gt; a sample question of what kind of pets do you have... i want a chart to
gt;gt; represent the one question quot;how many pets do you havequot; and the data in
gt;gt; the chart to reflect the answers collected... soooo... if 5 answered dog,
gt;gt; and 7 answered cats. i want the chart to
gt;gt; show this as if its drawn up a chart from numerical data to begin with.
gt;gt; see below for further detail...
gt;gt;
gt;gt; --------------------------------
gt;gt;
gt;gt; Question: what is your fav animal?
gt;gt; dog
gt;gt; cat
gt;gt; dog
gt;gt; dog
gt;gt; dog
gt;gt; cat
gt;gt; cat
gt;gt; monkey
gt;gt; elephant
gt;gt; ..
gt;gt; .
gt;gt; .
gt;gt;
gt;gt; i want the above data to be graphed into a nice pie or bar chart
gt;gt; representing the percentages shown proportionally... IS THIS EVEN
gt;gt; POSSIBLE???
gt;gt; ---------------------------------------------------------
gt;gt;
gt;gt; cheers.
gt;gt;
gt;gt;
gt;
gt; --
gt;
gt; Andy Pope, Microsoft MVP - Excel
gt; www.andypope.info
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

Lon wrote:
gt; Yes, excellent indeed. Thanks.
gt;
gt; A follow-up question, if I may: Why do you use absolute locations ($A$1) in
gt; your forumlas, rather than plain cell locations? The result, in this example
gt; at least, is the same.
gt;
gt; -Lon
gt;
gt;
gt; quot;Andy Popequot; gt; wrote in message
gt; ...
gt;
gt;gt;[This is a reply I gave to a very similar question the other day.
gt;gt;Just replace Apple/Orange with Cat/Dog]
gt;gt;
gt;gt;Hi,
gt;gt;
gt;gt;Excel will not automatically summarize you textual values.
gt;gt;For example if your data, in A1:A6, was;
gt;gt;
gt;gt;Apple
gt;gt;Orange
gt;gt;Orange
gt;gt;Orange
gt;gt;Apple
gt;gt;Apple
gt;gt;
gt;gt;you would not get a 2 slice pie chart with each slice being 50%. You need
gt;gt;to summarize the data using formula or pivot table. The create the pie
gt;gt;chart on the summary data.
gt;gt;
gt;gt;Here is an example of summarizing the example data.
gt;gt;B1: =quot;Applequot; C1: =COUNTIF($A$1:$A$6,B1)
gt;gt;B2: =quot;Orangequot; C2: =COUNTIF($A$1:$A$6,B2)
gt;gt;
gt;gt;The select B1:C2 in order to create your chart
gt;gt;
gt;gt;Cheers
gt;gt;Andy
gt;gt;
gt;gt;TheDevil wrote:
gt;gt;
gt;gt;gt;Hi all,
gt;gt;gt;
gt;gt;gt;i've got this worksheet going. the data is a collection of stats from a
gt;gt;gt;market research. its got name, company, address questions and answers.
gt;gt;gt;some are simple answers like yes no, others are multiple choices.
gt;gt;gt;
gt;gt;gt;so basically i want to draw up charts for this. but i figure i can only
gt;gt;gt;do this with numerical data not textual data. is there a formula or way
gt;gt;gt;around it?
gt;gt;gt;
gt;gt;gt;i am absolutely clueless how to do it. so if it works out i want a pie
gt;gt;gt;chart or bar chart that represents the
gt;gt;gt;question and the percentages of how the answers were received. e.g. for
gt;gt;gt;a sample question of what kind of pets do you have... i want a chart to
gt;gt;gt;represent the one question quot;how many pets do you havequot; and the data in
gt;gt;gt;the chart to reflect the answers collected... soooo... if 5 answered dog,
gt;gt;gt;and 7 answered cats. i want the chart to
gt;gt;gt;show this as if its drawn up a chart from numerical data to begin with.
gt;gt;gt;see below for further detail...
gt;gt;gt;
gt;gt;gt;--------------------------------
gt;gt;gt;
gt;gt;gt;Question: what is your fav animal?
gt;gt;gt;dog
gt;gt;gt;cat
gt;gt;gt;dog
gt;gt;gt;dog
gt;gt;gt;dog
gt;gt;gt;cat
gt;gt;gt;cat
gt;gt;gt;monkey
gt;gt;gt;elephant
gt;gt;gt;..
gt;gt;gt;.
gt;gt;gt;.
gt;gt;gt;
gt;gt;gt;i want the above data to be graphed into a nice pie or bar chart
gt;gt;gt;representing the percentages shown proportionally... IS THIS EVEN
gt;gt;gt;POSSIBLE???
gt;gt;gt; ---------------------------------------------------------
gt;gt;gt;
gt;gt;gt;cheers.
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;--
gt;gt;
gt;gt;Andy Pope, Microsoft MVP - Excel
gt;gt;www.andypope.info
gt;
gt;
gt;

--

Andy Pope, Microsoft MVP - Excel
www.andypope.info

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

quot;Andy Popequot; gt; wrote in message
...
gt; The combination of absolute and relative references allows me to type the
gt; formula in C1 and fill down automatically. The reference to the table of
gt; data A1:A6 will remain fixed whilst the reference to the lookup value in
gt; B1:B2 will automatically update.
gt;
gt; Cheers
gt; Andy
gt;
gt; Lon wrote:
gt;gt; Yes, excellent indeed. Thanks.
gt;gt;
gt;gt; A follow-up question, if I may: Why do you use absolute locations ($A$1)
gt;gt; in your forumlas, rather than plain cell locations? The result, in this
gt;gt; example at least, is the same.
gt;gt;
gt;gt; -Lon
gt;gt;
gt;gt;
gt;gt; quot;Andy Popequot; gt; wrote in message
gt;gt; ...
gt;gt;
gt;gt;gt;[This is a reply I gave to a very similar question the other day.
gt;gt;gt;Just replace Apple/Orange with Cat/Dog]
gt;gt;gt;
gt;gt;gt;Hi,
gt;gt;gt;
gt;gt;gt;Excel will not automatically summarize you textual values.
gt;gt;gt;For example if your data, in A1:A6, was;
gt;gt;gt;
gt;gt;gt;Apple
gt;gt;gt;Orange
gt;gt;gt;Orange
gt;gt;gt;Orange
gt;gt;gt;Apple
gt;gt;gt;Apple
gt;gt;gt;
gt;gt;gt;you would not get a 2 slice pie chart with each slice being 50%. You need
gt;gt;gt;to summarize the data using formula or pivot table. The create the pie
gt;gt;gt;chart on the summary data.
gt;gt;gt;
gt;gt;gt;Here is an example of summarizing the example data.
gt;gt;gt;B1: =quot;Applequot; C1: =COUNTIF($A$1:$A$6,B1)
gt;gt;gt;B2: =quot;Orangequot; C2: =COUNTIF($A$1:$A$6,B2)
gt;gt;gt;
gt;gt;gt;The select B1:C2 in order to create your chart
gt;gt;gt;
gt;gt;gt;Cheers
gt;gt;gt;Andy
gt;gt;gt;
gt;gt;gt;TheDevil wrote:
gt;gt;gt;
gt;gt;gt;gt;Hi all,
gt;gt;gt;gt;
gt;gt;gt;gt;i've got this worksheet going. the data is a collection of stats from a
gt;gt;gt;gt;market research. its got name, company, address questions and answers.
gt;gt;gt;gt;some are simple answers like yes no, others are multiple choices.
gt;gt;gt;gt;
gt;gt;gt;gt;so basically i want to draw up charts for this. but i figure i can only
gt;gt;gt;gt;do this with numerical data not textual data. is there a formula or way
gt;gt;gt;gt;around it?
gt;gt;gt;gt;
gt;gt;gt;gt;i am absolutely clueless how to do it. so if it works out i want a pie
gt;gt;gt;gt;chart or bar chart that represents the
gt;gt;gt;gt;question and the percentages of how the answers were received. e.g. for
gt;gt;gt;gt;a sample question of what kind of pets do you have... i want a chart to
gt;gt;gt;gt;represent the one question quot;how many pets do you havequot; and the data in
gt;gt;gt;gt;the chart to reflect the answers collected... soooo... if 5 answered
gt;gt;gt;gt;dog, and 7 answered cats. i want the chart to
gt;gt;gt;gt;show this as if its drawn up a chart from numerical data to begin with.
gt;gt;gt;gt;see below for further detail...
gt;gt;gt;gt;
gt;gt;gt;gt;--------------------------------
gt;gt;gt;gt;
gt;gt;gt;gt;Question: what is your fav animal?
gt;gt;gt;gt;dog
gt;gt;gt;gt;cat
gt;gt;gt;gt;dog
gt;gt;gt;gt;dog
gt;gt;gt;gt;dog
gt;gt;gt;gt;cat
gt;gt;gt;gt;cat
gt;gt;gt;gt;monkey
gt;gt;gt;gt;elephant
gt;gt;gt;gt;..
gt;gt;gt;gt;.
gt;gt;gt;gt;.
gt;gt;gt;gt;
gt;gt;gt;gt;i want the above data to be graphed into a nice pie or bar chart
gt;gt;gt;gt;representing the percentages shown proportionally... IS THIS EVEN
gt;gt;gt;gt;POSSIBLE???
gt;gt;gt;gt; ---------------------------------------------------------
gt;gt;gt;gt;
gt;gt;gt;gt;cheers.
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;
gt;gt;gt;--
gt;gt;gt;
gt;gt;gt;Andy Pope, Microsoft MVP - Excel
gt;gt;gt;www.andypope.info
gt;gt;
gt;gt;
gt;gt;
gt;
gt; --
gt;
gt; Andy Pope, Microsoft MVP - Excel
gt; www.andypope.info