If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Third Party Charting Add-ons/Plug-ins/Controls | Hans Wolf | Charts and Charting | 5 | June 18th, 2005 01:14 PM |
Union query with left join | Abdula Zakaria via AccessMonster.com | Running & Setting Up Queries | 4 | April 23rd, 2005 11:58 AM |
Dynamic charting problems (events) | [email protected] | Charts and Charting | 4 | January 27th, 2005 09:32 PM |
Charting time? help please... | Gustavo Monteverde | Charts and Charting | 1 | November 28th, 2004 04:53 AM |
Interesting Web blog on Excel charting obsessions | Eva Whitley | Charts and Charting | 1 | May 19th, 2004 01:57 AM |