| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting Textual Statistics
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. -- TheDevil ------------------------------------------------------------------------ TheDevil's Profile: http://www.excelforum.com/member.php...o&userid=33708 View this thread: http://www.excelforum.com/showthread...hreadid=534879 |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Charting Textual Statistics
[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. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Charting Textual Statistics
Wow! Excellent! Thanks for that! Cheers TheDevil -- TheDevil ------------------------------------------------------------------------ TheDevil's Profile: http://www.excelforum.com/member.php...o&userid=33708 View this thread: http://www.excelforum.com/showthread...hreadid=534879 |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Charting Textual Statistics
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 "Andy Pope" wrote in message ... [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. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Charting Textual Statistics
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: 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 "Andy Pope" wrote in message ... [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. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
|
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Charting Textual Statistics
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 "Andy Pope" wrote in message ... 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: 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 "Andy Pope" wrote in message ... [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. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Third Party Charting Add-ons/Plug-ins/Controls | Charts and Charting in Excel | |||
| training for Pivot Charting in Excel 2000 | Excel Discussion (Misc queries) | |||
| Custom charting - Stacked charting with a line | Charts and Charting in Excel | |||
| Dynamic charting problems (events) | Charts and Charting in Excel | |||
| Charting time? help please... | Charts and Charting in Excel | |||
Linear Mode
