| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
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???  --------------------------------------------------------- cheers -- TheDevi ----------------------------------------------------------------------- TheDevil's Profile: http://www.excelforum.com/member.php...fo&userid=3370 View this thread: http://www.excelforum.com/showthread.php?threadid=53487 |
|
|
|
|||
|
|
| |
|
Guest
Posts: n/a
|
[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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > [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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... > >>[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 |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message >> news:(E-Mail Removed)... >> >>>[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 | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Re: Textual value axes | Kelly O'Day | Microsoft Excel Charting | 5 | 14th Dec 2006 03:42 PM |
| Statistics/Charting Question - Please Help!! | =?Utf-8?B?ZWxmbWFqZXN0eQ==?= | Microsoft Excel Misc | 2 | 13th Jan 2006 07:59 PM |
| Custom charting - Stacked charting with a line | Randy Lefferts | Microsoft Excel Charting | 3 | 3rd Mar 2005 03:10 AM |
| Textual form of ClassView? | =?Utf-8?B?VG9tIERvc3Rlcg==?= | Microsoft VC .NET | 0 | 30th May 2004 09:16 PM |
| Textual form of ClassView? | =?Utf-8?B?VG9t?= | Microsoft VC .NET | 0 | 30th May 2004 09:11 PM |
