| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
pie-charting non-numeric data
Jon,
I am following the advice given but my pivot table keeps defining 'Yes' or 'No' as a value of 1. So when it's listing whether employee no. x used a, b or c it counts 'yes' and 'no' as '1' each so even when employee only used a and not b or c it still classes them both as a positive (1) and so my totals are coming out '3' everytime because there are 3 options, even when I have only put 'Yes' for 1 option. What am I doing wrong? Gina "Jon Peltier" wrote: Doug - Take the survey data, laid out like this: color fruit 1 red apple 2 green orange 3 red orange 4 green banana 5 blue apple 6 red grapes 7 green apple 8 red orange 9 green orange 10 blue banana and rearrange it like this: item value 1 color red 2 color green 3 color red 4 color green 5 color blue 6 color red 7 color green 8 color red 9 color green 10 color blue 1 fruit apple 2 fruit orange 3 fruit orange 4 fruit banana 5 fruit apple 6 fruit grapes 7 fruit apple 8 fruit orange 9 fruit orange 10 fruit banana Make a pivot table, putting item and value in the Rows area and Count of value in the Data area: Count of value item value Total color blue 2 green 4 red 4 fruit apple 3 banana 2 grapes 1 orange 4 One pivot table contains all the data. You need to make a non-pivot chart to graph only some of this data, select a blank cell not touching the pivot table, start the chart wizard. Step 1, select a pie chart. Step 2, click on the Series tab, then click Add, and for categories select the range of colors and for values select the totals next to the colors. There's your first chart. Make a copy of the chart, click on the pie, and drag and resize the purple and blue highlight rectangles to change from the colors to the fruits. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Doug VanOrnum wrote: Thanks Jon -- a clarification: One analogy would be a column of "favorite colors". Maybe there are six colors to choose from. A "pivot table" would count the number of instances of "orange", for example, as well as the other colors? Then I could make a pie chart of the pivot table? Hmmm...so that means if my questionnaire has 45 questions total, I'd have to set up a separate pivot table for each question, then make a chart or graph for each...sounds pretty inefficient. Is there a better path I need to take to get to the desired end-result? "Jon Peltier" wrote: So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value. Then make a chart of these counts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Doug VanOrnum wrote: I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? |
|
#2
|
|||
|
|||
|
If your source data is like this:
Name Question Response Joe a Yes Joe b No Joe c No Sam a No In the pivot table, put Name and Question in the row area, Response in the column area, and another copy of Response in the Data area, where it will become Count of Response. Gina O'Brien wrote: Jon, I am following the advice given but my pivot table keeps defining 'Yes' or 'No' as a value of 1. So when it's listing whether employee no. x used a, b or c it counts 'yes' and 'no' as '1' each so even when employee only used a and not b or c it still classes them both as a positive (1) and so my totals are coming out '3' everytime because there are 3 options, even when I have only put 'Yes' for 1 option. What am I doing wrong? Gina "Jon Peltier" wrote: Doug - Take the survey data, laid out like this: color fruit 1 red apple 2 green orange 3 red orange 4 green banana 5 blue apple 6 red grapes 7 green apple 8 red orange 9 green orange 10 blue banana and rearrange it like this: item value 1 color red 2 color green 3 color red 4 color green 5 color blue 6 color red 7 color green 8 color red 9 color green 10 color blue 1 fruit apple 2 fruit orange 3 fruit orange 4 fruit banana 5 fruit apple 6 fruit grapes 7 fruit apple 8 fruit orange 9 fruit orange 10 fruit banana Make a pivot table, putting item and value in the Rows area and Count of value in the Data area: Count of value item value Total color blue 2 green 4 red 4 fruit apple 3 banana 2 grapes 1 orange 4 One pivot table contains all the data. You need to make a non-pivot chart to graph only some of this data, select a blank cell not touching the pivot table, start the chart wizard. Step 1, select a pie chart. Step 2, click on the Series tab, then click Add, and for categories select the range of colors and for values select the totals next to the colors. There's your first chart. Make a copy of the chart, click on the pie, and drag and resize the purple and blue highlight rectangles to change from the colors to the fruits. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Doug VanOrnum wrote: Thanks Jon -- a clarification: One analogy would be a column of "favorite colors". Maybe there are six colors to choose from. A "pivot table" would count the number of instances of "orange", for example, as well as the other colors? Then I could make a pie chart of the pivot table? Hmmm...so that means if my questionnaire has 45 questions total, I'd have to set up a separate pivot table for each question, then make a chart or graph for each...sounds pretty inefficient. Is there a better path I need to take to get to the desired end-result? "Jon Peltier" wrote: So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value. Then make a chart of these counts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Doug VanOrnum wrote: I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#3
|
|||
|
|||
|
Ok: having tried your Debra advice I think what I many need is slightly
different than I thought! Because my responses have more than one choice, e.g., Name Consultant Physio Other Treatment Joe Ys No No Jane No Yes No Sam No No Yes etc. I think that is what is confusing the pivot table as what I'm putting in is not a count up of 1 column of yes or no's but 3 options (consultant, physio & other treatment) or yes or no's. At the moment it's just giving me a confused jumble! Can it be done?! "Debra Dalgleish" wrote: If your source data is like this: Name Question Response Joe a Yes Joe b No Joe c No Sam a No In the pivot table, put Name and Question in the row area, Response in the column area, and another copy of Response in the Data area, where it will become Count of Response. Gina O'Brien wrote: Jon, I am following the advice given but my pivot table keeps defining 'Yes' or 'No' as a value of 1. So when it's listing whether employee no. x used a, b or c it counts 'yes' and 'no' as '1' each so even when employee only used a and not b or c it still classes them both as a positive (1) and so my totals are coming out '3' everytime because there are 3 options, even when I have only put 'Yes' for 1 option. What am I doing wrong? Gina "Jon Peltier" wrote: Doug - Take the survey data, laid out like this: color fruit 1 red apple 2 green orange 3 red orange 4 green banana 5 blue apple 6 red grapes 7 green apple 8 red orange 9 green orange 10 blue banana and rearrange it like this: item value 1 color red 2 color green 3 color red 4 color green 5 color blue 6 color red 7 color green 8 color red 9 color green 10 color blue 1 fruit apple 2 fruit orange 3 fruit orange 4 fruit banana 5 fruit apple 6 fruit grapes 7 fruit apple 8 fruit orange 9 fruit orange 10 fruit banana Make a pivot table, putting item and value in the Rows area and Count of value in the Data area: Count of value item value Total color blue 2 green 4 red 4 fruit apple 3 banana 2 grapes 1 orange 4 One pivot table contains all the data. You need to make a non-pivot chart to graph only some of this data, select a blank cell not touching the pivot table, start the chart wizard. Step 1, select a pie chart. Step 2, click on the Series tab, then click Add, and for categories select the range of colors and for values select the totals next to the colors. There's your first chart. Make a copy of the chart, click on the pie, and drag and resize the purple and blue highlight rectangles to change from the colors to the fruits. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Doug VanOrnum wrote: Thanks Jon -- a clarification: One analogy would be a column of "favorite colors". Maybe there are six colors to choose from. A "pivot table" would count the number of instances of "orange", for example, as well as the other colors? Then I could make a pie chart of the pivot table? Hmmm...so that means if my questionnaire has 45 questions total, I'd have to set up a separate pivot table for each question, then make a chart or graph for each...sounds pretty inefficient. Is there a better path I need to take to get to the desired end-result? "Jon Peltier" wrote: So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value. Then make a chart of these counts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Doug VanOrnum wrote: I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#4
|
|||
|
|||
|
Gina -
What you're doing is determining the numbers or percentages of each answer to each question. Sample data: Name Ques Response Joe a Yes Joe b No Joe c No Joe d 1 Sam a No Sam b Maybe Sam c No Sam d 3 Bill a No Bill b No Bill c Maybe Bill d 2 George a Maybe George b Yes George c Yes George d 1 There are many ways to proceed. Pivot Table 1, drag Question then Response to the Column area, then drag another copy of Resopnse to the Data Area: a b c d Yes Maybe No Yes Maybe No Yes Maybe No 1 2 3 Total 1 1 2 1 1 2 1 1 2 2 1 1 Plot each separate piece of the table, e.g., a Yes Maybe No 1 1 2 in a regular pie chart. To make a non-pivot chart from a pivot table, select a blank cell away from the table and run the Chart Wizard. In step 1, choose a chart type as always. In step 2, click on the Series tab, and define each series independently here. Then continue with the Wizard. Pivot Table 2, drag Question to the Row area and Response to the Column Area, then drag another copy of Resopnse to the Data Area: 1 2 3 Yes Maybe No a 1 1 2 b 1 1 2 c 1 1 2 d 2 1 1 You can make a stacked column chart (or stacked 100% column chart), and even a dumb Pivot Chart is passable. Each question is listed along the bottom of the chart, and the proportions of each response are stacked up. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Gina O'Brien wrote: Ok: having tried your Debra advice I think what I many need is slightly different than I thought! Because my responses have more than one choice, e.g., Name Consultant Physio Other Treatment Joe Ys No No Jane No Yes No Sam No No Yes etc. I think that is what is confusing the pivot table as what I'm putting in is not a count up of 1 column of yes or no's but 3 options (consultant, physio & other treatment) or yes or no's. At the moment it's just giving me a confused jumble! Can it be done?! "Debra Dalgleish" wrote: If your source data is like this: Name Question Response Joe a Yes Joe b No Joe c No Sam a No In the pivot table, put Name and Question in the row area, Response in the column area, and another copy of Response in the Data area, where it will become Count of Response. Gina O'Brien wrote: Jon, I am following the advice given but my pivot table keeps defining 'Yes' or 'No' as a value of 1. So when it's listing whether employee no. x used a, b or c it counts 'yes' and 'no' as '1' each so even when employee only used a and not b or c it still classes them both as a positive (1) and so my totals are coming out '3' everytime because there are 3 options, even when I have only put 'Yes' for 1 option. What am I doing wrong? Gina "Jon Peltier" wrote: Doug - Take the survey data, laid out like this: color fruit 1 red apple 2 green orange 3 red orange 4 green banana 5 blue apple 6 red grapes 7 green apple 8 red orange 9 green orange 10 blue banana and rearrange it like this: item value 1 color red 2 color green 3 color red 4 color green 5 color blue 6 color red 7 color green 8 color red 9 color green 10 color blue 1 fruit apple 2 fruit orange 3 fruit orange 4 fruit banana 5 fruit apple 6 fruit grapes 7 fruit apple 8 fruit orange 9 fruit orange 10 fruit banana Make a pivot table, putting item and value in the Rows area and Count of value in the Data area: Count of value item value Total color blue 2 green 4 red 4 fruit apple 3 banana 2 grapes 1 orange 4 One pivot table contains all the data. You need to make a non-pivot chart to graph only some of this data, select a blank cell not touching the pivot table, start the chart wizard. Step 1, select a pie chart. Step 2, click on the Series tab, then click Add, and for categories select the range of colors and for values select the totals next to the colors. There's your first chart. Make a copy of the chart, click on the pie, and drag and resize the purple and blue highlight rectangles to change from the colors to the fruits. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Doug VanOrnum wrote: Thanks Jon -- a clarification: One analogy would be a column of "favorite colors". Maybe there are six colors to choose from. A "pivot table" would count the number of instances of "orange", for example, as well as the other colors? Then I could make a pie chart of the pivot table? Hmmm...so that means if my questionnaire has 45 questions total, I'd have to set up a separate pivot table for each question, then make a chart or graph for each...sounds pretty inefficient. Is there a better path I need to take to get to the desired end-result? "Jon Peltier" wrote: So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value. Then make a chart of these counts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Doug VanOrnum wrote: I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#5
|
|||
|
|||
|
Thank you Jon, I've finally cracked it!
Gina. "Jon Peltier" wrote: Gina - What you're doing is determining the numbers or percentages of each answer to each question. Sample data: Name Ques Response Joe a Yes Joe b No Joe c No Joe d 1 Sam a No Sam b Maybe Sam c No Sam d 3 Bill a No Bill b No Bill c Maybe Bill d 2 George a Maybe George b Yes George c Yes George d 1 There are many ways to proceed. Pivot Table 1, drag Question then Response to the Column area, then drag another copy of Resopnse to the Data Area: a b c d Yes Maybe No Yes Maybe No Yes Maybe No 1 2 3 Total 1 1 2 1 1 2 1 1 2 2 1 1 Plot each separate piece of the table, e.g., a Yes Maybe No 1 1 2 in a regular pie chart. To make a non-pivot chart from a pivot table, select a blank cell away from the table and run the Chart Wizard. In step 1, choose a chart type as always. In step 2, click on the Series tab, and define each series independently here. Then continue with the Wizard. Pivot Table 2, drag Question to the Row area and Response to the Column Area, then drag another copy of Resopnse to the Data Area: 1 2 3 Yes Maybe No a 1 1 2 b 1 1 2 c 1 1 2 d 2 1 1 You can make a stacked column chart (or stacked 100% column chart), and even a dumb Pivot Chart is passable. Each question is listed along the bottom of the chart, and the proportions of each response are stacked up. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Gina O'Brien wrote: Ok: having tried your Debra advice I think what I many need is slightly different than I thought! Because my responses have more than one choice, e.g., Name Consultant Physio Other Treatment Joe Ys No No Jane No Yes No Sam No No Yes etc. I think that is what is confusing the pivot table as what I'm putting in is not a count up of 1 column of yes or no's but 3 options (consultant, physio & other treatment) or yes or no's. At the moment it's just giving me a confused jumble! Can it be done?! "Debra Dalgleish" wrote: If your source data is like this: Name Question Response Joe a Yes Joe b No Joe c No Sam a No In the pivot table, put Name and Question in the row area, Response in the column area, and another copy of Response in the Data area, where it will become Count of Response. Gina O'Brien wrote: Jon, I am following the advice given but my pivot table keeps defining 'Yes' or 'No' as a value of 1. So when it's listing whether employee no. x used a, b or c it counts 'yes' and 'no' as '1' each so even when employee only used a and not b or c it still classes them both as a positive (1) and so my totals are coming out '3' everytime because there are 3 options, even when I have only put 'Yes' for 1 option. What am I doing wrong? Gina "Jon Peltier" wrote: Doug - Take the survey data, laid out like this: color fruit 1 red apple 2 green orange 3 red orange 4 green banana 5 blue apple 6 red grapes 7 green apple 8 red orange 9 green orange 10 blue banana and rearrange it like this: item value 1 color red 2 color green 3 color red 4 color green 5 color blue 6 color red 7 color green 8 color red 9 color green 10 color blue 1 fruit apple 2 fruit orange 3 fruit orange 4 fruit banana 5 fruit apple 6 fruit grapes 7 fruit apple 8 fruit orange 9 fruit orange 10 fruit banana Make a pivot table, putting item and value in the Rows area and Count of value in the Data area: Count of value item value Total color blue 2 green 4 red 4 fruit apple 3 banana 2 grapes 1 orange 4 One pivot table contains all the data. You need to make a non-pivot chart to graph only some of this data, select a blank cell not touching the pivot table, start the chart wizard. Step 1, select a pie chart. Step 2, click on the Series tab, then click Add, and for categories select the range of colors and for values select the totals next to the colors. There's your first chart. Make a copy of the chart, click on the pie, and drag and resize the purple and blue highlight rectangles to change from the colors to the fruits. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Doug VanOrnum wrote: Thanks Jon -- a clarification: One analogy would be a column of "favorite colors". Maybe there are six colors to choose from. A "pivot table" would count the number of instances of "orange", for example, as well as the other colors? Then I could make a pie chart of the pivot table? Hmmm...so that means if my questionnaire has 45 questions total, I'd have to set up a separate pivot table for each question, then make a chart or graph for each...sounds pretty inefficient. Is there a better path I need to take to get to the desired end-result? "Jon Peltier" wrote: So you have a column of Yes/No or P/K or J/N? Construct a pivot table of this range, and you can produce counts of each value. Then make a chart of these counts. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Doug VanOrnum wrote: I would like to chart and graph responses to a survey that are non-numeric in nature, such as a bar graph for "yes" and no" responses to a question. Or in particular a pie chart that shows how many people picked option "P" vs. "K", "J" or "N". For the pie chart I have a column of data cells, each containing one of the four letters. In short, how do I accomplish charting and/or graphing non-numeric responses? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sort pages? | Excel Discussion (Misc queries) | |||
| non numeric pie charting | New Users to Excel | |||
| Why " data analysis plus " override " data analysis " once instal. | Excel Worksheet Functions | |||
| Fetching External Data from Excel | Excel Discussion (Misc queries) | |||
| To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Linear Mode
