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%)

Trend	Survey 1	Survey 2
	Temporary	Not Sure	Permanent	Temporary	Not Sure	Permanent
Apples	3.80%	1.00%	95.40%	2.78%	0.46%	96.76%
Pears	1.70%	4.00%	94.30%	0.47%	0.46%	99.07%

<tbody>
</tbody><colgroup><col><col span="6"></colgroup>

Any pointers please?

 

[oldbrewer]

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

 

[oldbrewer]

	Survey 1			Survey 2
Temporary	Not Sure	Permanent	Temporary	Not Sure	Permanent
Apples	3.80%	1.00%	95.40%	2.78%	0.46%	96.76%
Pears	1.70%	4.00%	94.30%	0.47%	0.46%	99.07%
	3.8	2.78	1.7	0.47
	1	0.46	4	0.46
	95.2	96.76	94.3	99.07
	by setting out the data as above
	and choosing stacked bar chart
	4 stacked bars are plotted
	but difficult to compare eg 1 with .46
	on a 0 to 100 scale
	apples pears and surveys 1 and 1
	can be manually added to the chart

<colgroup><col span="2"><col><col span="5"></colgroup><tbody>
</tbody>

 

[thisoldman]

Try sorting your data like this:

	A	B	C	D	E
1			Permanent	Not Sure	Temporary
2	Apples	Survey 1	95.40%	1.00%	3.80%
3		Survey 2	96.76%	0.46%	2.78%
4	Pears	Survey 1	94.30%	4.00%	1.70%
5		Survey 2	99.07%	0.46%	0.47%

<tbody>
</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 -

Trend	Survey 1	Survey 2	Survey 1	Survey 2	Survey 1	Survey 2
	Temporary	Not Sure	Permanent
Apples	1.00%	2.78%	1.00%	0.46%	95.40%	96.76%
Pears	1.70%	0.47%	4.00%	0.46%	94.30%	99.07%

<tbody>
</tbody>

But it didn't work?

It stacked Survey 1 and 2 together again

 

[earwig999]

Try sorting your data like this:

	A	B	C	D	E
1			Permanent	Not Sure	Temporary
2	Apples	Survey 1	95.40%	1.00%	3.80%
3		Survey 2	96.76%	0.46%	2.78%
4	Pears	Survey 1	94.30%	4.00%	1.70%
5		Survey 2	99.07%	0.46%	0.47%

<tbody>
</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.