Chart to compare two data sets

[earwig999]

I'm trying to produce an excel chart to show responses to a survey from two different sources.

e.g.

What trends do you think are permanent or temporary?

Apples - P,T,NS
Pears - P,T,NS

Now there are results from a National survey and then results from Local survey. The options people select are Permanent, Temporary or Not Sure (I want these to show as stacked, but side by side for Survey 1 and Survey 2)

When I stack it puts Apples and Pears on top of each other making 200% - I want Survey 1 and Survey 2 side by side but each as a single column containing P,T & NS (adding up to 100%)

[TABLE="width: 682"]
<tbody>[TR]
[TD]Trend[/TD]
[TD="colspan: 3"]Survey 1[/TD]
[TD="colspan: 3"]Survey 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Temporary[/TD]
[TD]Not Sure[/TD]
[TD]Permanent[/TD]
[TD]Temporary[/TD]
[TD]Not Sure[/TD]
[TD]Permanent[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]3.80%[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"]95.40%[/TD]
[TD="align: right"]2.78%[/TD]
[TD="align: right"]0.46%[/TD]
[TD="align: right"]96.76%[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="align: right"]1.70%[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]94.30%[/TD]
[TD="align: right"]0.47%[/TD]
[TD="align: right"]0.46%[/TD]
[TD="align: right"]99.07%[/TD]
[/TR]
</tbody><colgroup><col><col span="6"></colgroup>[/TABLE]


Any pointers please?

 

[oldbrewer]

so you want 2 stacks for apples and 2 for pears all on one chart ?

 

[oldbrewer]

[TABLE="width: 526"]
<colgroup><col span="2"><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Survey 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Survey 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Temporary[/TD]
[TD]Not Sure[/TD]
[TD]Permanent[/TD]
[TD]Temporary[/TD]
[TD]Not Sure[/TD]
[TD="colspan: 2"]Permanent[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]3.80%[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"]95.40%[/TD]
[TD="align: right"]2.78%[/TD]
[TD="align: right"]0.46%[/TD]
[TD="align: right"]96.76%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="align: right"]1.70%[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]94.30%[/TD]
[TD="align: right"]0.47%[/TD]
[TD="align: right"]0.46%[/TD]
[TD="align: right"]99.07%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3.8[/TD]
[TD="align: right"]2.78[/TD]
[TD="align: right"]1.7[/TD]
[TD="align: right"]0.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.46[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]95.2[/TD]
[TD="align: right"]96.76[/TD]
[TD="align: right"]94.3[/TD]
[TD="align: right"]99.07[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]by setting out the data as above[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]and choosing stacked bar chart[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]4 stacked bars are plotted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]but difficult to compare eg 1 with .46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]on a 0 to 100 scale[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]apples pears and surveys 1 and 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]can be manually added to the chart[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[thisoldman]

Try sorting your data like this:

<tbody>
[TD="class: xl65"][/TD]
[TD="class: xl65"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]

[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Permanent[/TD]
[TD="class: xl65"]Not Sure[/TD]
[TD="class: xl65"]Temporary[/TD]

[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl67"]Apples[/TD]
[TD="class: xl65"]Survey 1[/TD]
[TD="class: xl66, align: right"]95.40%[/TD]
[TD="class: xl66, align: right"]1.00%[/TD]
[TD="class: xl66, align: right"]3.80%[/TD]

[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"]Survey 2[/TD]
[TD="class: xl66, align: right"]96.76%[/TD]
[TD="class: xl66, align: right"]0.46%[/TD]
[TD="class: xl66, align: right"]2.78%[/TD]

[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl67"]Pears[/TD]
[TD="class: xl65"]Survey 1[/TD]
[TD="class: xl66, align: right"]94.30%[/TD]
[TD="class: xl66, align: right"]4.00%[/TD]
[TD="class: xl66, align: right"]1.70%[/TD]

[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"]Survey 2[/TD]
[TD="class: xl66, align: right"]99.07%[/TD]
[TD="class: xl66, align: right"]0.46%[/TD]
[TD="class: xl66, align: right"]0.47%[/TD]

</tbody>

Select the data, A1:E5, and insert a stacked bar chart. Adjust the horizontal axis to start at zero.
Adjust the vertical axis to have categories in reverse order.

Note that I switched Permanent to be first in the series order.

 

[earwig999]

I did this as stated - [TABLE="width: 682"]
<tbody>[TR]
[TD]Trend
[/TD]
[TD]Survey 1
[/TD]
[TD]Survey 2
[/TD]
[TD]Survey 1
[/TD]
[TD]Survey 2
[/TD]
[TD]Survey 1
[/TD]
[TD]Survey 2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Temporary
[/TD]
[TD="colspan: 2"]Not Sure
[/TD]
[TD="colspan: 2"]Permanent
[/TD]
[/TR]
[TR]
[TD]Apples
[/TD]
[TD="align: right"]1.00%
[/TD]
[TD="align: right"]2.78%
[/TD]
[TD="align: right"]1.00%
[/TD]
[TD="align: right"]0.46%
[/TD]
[TD="align: right"]95.40%
[/TD]
[TD="align: right"]96.76%
[/TD]
[/TR]
[TR]
[TD]Pears
[/TD]
[TD="align: right"]1.70%
[/TD]
[TD="align: right"]0.47%
[/TD]
[TD="align: right"]4.00%
[/TD]
[TD="align: right"]0.46%
[/TD]
[TD="align: right"]94.30%
[/TD]
[TD="align: right"]99.07%
[/TD]
[/TR]
</tbody>[/TABLE]


But it didn't work?

It stacked Survey 1 and 2 together again

 

[earwig999]

Try sorting your data like this:

<tbody>
[TD="class: xl65"][/TD]
[TD="class: xl65"]A
[/TD]
[TD="class: xl65"]B
[/TD]
[TD="class: xl65"]C
[/TD]
[TD="class: xl65, width: 64"]D
[/TD]
[TD="class: xl65, width: 64"]E
[/TD]

[TD="class: xl65, align: right"]1
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Permanent
[/TD]
[TD="class: xl65"]Not Sure
[/TD]
[TD="class: xl65"]Temporary
[/TD]

[TD="class: xl65, align: right"]2
[/TD]
[TD="class: xl67"]Apples
[/TD]
[TD="class: xl65"]Survey 1
[/TD]
[TD="class: xl66, align: right"]95.40%
[/TD]
[TD="class: xl66, align: right"]1.00%
[/TD]
[TD="class: xl66, align: right"]3.80%
[/TD]

[TD="class: xl65, align: right"]3
[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"]Survey 2
[/TD]
[TD="class: xl66, align: right"]96.76%
[/TD]
[TD="class: xl66, align: right"]0.46%
[/TD]
[TD="class: xl66, align: right"]2.78%
[/TD]

[TD="class: xl65, align: right"]4
[/TD]
[TD="class: xl67"]Pears
[/TD]
[TD="class: xl65"]Survey 1
[/TD]
[TD="class: xl66, align: right"]94.30%
[/TD]
[TD="class: xl66, align: right"]4.00%
[/TD]
[TD="class: xl66, align: right"]1.70%
[/TD]

[TD="class: xl65, align: right"]5
[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"]Survey 2
[/TD]
[TD="class: xl66, align: right"]99.07%
[/TD]
[TD="class: xl66, align: right"]0.46%
[/TD]
[TD="class: xl66, align: right"]0.47%
[/TD]

</tbody>

Select the data, A1:E5, and insert a stacked bar chart. Adjust the horizontal axis to start at zero.
Adjust the vertical axis to have categories in reverse order.

Note that I switched Permanent to be first in the series order.

This is working for me now this way! Thanks.