Complicated Bar Chart?

[nigidivitch]

Hi
I'm trying what appears to be a complicated bar chart and I'm struggling to get any decent results... Can anyone help?

I have six variables (V1, V2, V3, V4, V5, V6) which represent groups of people. Each of these groups have a male/female split, and so I am representing each variable's male/female split as a percentage.
i.e.
V1: 52.9% Male; 47.1% Female
V2: 52.7% Male; 47.3% Female
V3: 46.2% Male; 53.8% Female etc...

That has produced a very straightforward bar chart where each bar adds-up to 100%. Simples.

However, I also want to add, below each of the variable's (V1, V2, etc...) bars, another bar that splits each male/female group into three further sub-categories (X1, X2, & X3). So, of the 52.9% who are males within V1, X1=61.84%, X2=3.52%, and X3=34.64%; and of the 47.1% who are females witin V1, X1=19.28%, X2=26.65%, and X3=54.06%.
i.e.
V1: 52.9% Male 47.1% Female
V1': 61.84 X1, 3.52% X2, 34.64 X3; 19.28% X1, 26.65% X2, 54.06% X3

The problem is that Excel doesn't seem to like the fact that the bars representing variables V1-6 are split into two groups (male/female) whereas the bars representing the three further sub-categories are split into six (X1,X2,X3,X1,X2,X3) groups.

Have I made any sense there? These things are always quite tricky to explain...

Any help would be very appreciated!

Thanks

 

[Gerald Higgins]

Hi.

I think this will be straightforward, as long as you can get your source data laid out correctly.

Do you want the X1, X2 X3 etc components to actually be part of the V1-6 bars, or do you want them to be separate, perhaps along side them and hopefully the same size ?

 

[nigidivitch]

Thanks for getting back to me!

Yes, I want to show X1-3 alongside the V male/female bar.

So I want it to look something like this (I've changed X1-3 to x,y, & z to make it look better):

...............52.9%................../.... 47.1%.........
V1: M M M M M M M M M M M / F F F F F F F F F
V1': x x x x x y y y y y z z z z z/ x x x y y y y z z
........61.8%...3.52%....34.6%../19.3%.26.7%.54.1%

Now, to get the appropriate values for V1' x,y,z (because they currently add-up to 200%), I've multiplied each value for , y and x by its appropriate V1 male/female percentage:
eg.
61.8%*52.9% = 32.7% (therefore V1' X MALE = 32.7%)
3.5%*52.9% = 1.86% (thereofore V1' Y MALE = 1.86%)
34.7%*52.9% = 18.3% (thereofre V1' Z MALE = 18.34%)
19.3%*47.1% = 9.1% (therefore V1' X FEMALE = 9.1%)
26.7%*47.1% = 12.6% (therefore V1' Y FEMALE = 12.6%)
54.1%*47.1% = 25.5% (therefore V1' Y FEMALE = 25.5%)
So the sum of V1' now equals 100% and reflects the 52.9%/47.1% male female split highlighted in V1.

I've created two bar charts, one displaying V1-6, and the other displaying V1'-6', and these charts match-up nicely, but I can't work out how to put everything in one chart - the major problem seems to be that V1-6 bars have only two variables (male/female), and the V1'-6' bars have six (xyz twice)...

Thanks

 

[Gerald Higgins]

Yes OK.

You're going to need 8 data series altogether.

You'll need to lay your data out something like this

............M.....F.....MX.....MY.....MZ.....FX.....FY.....FZ
Row 1....53....47...................................................
Row 2..................33......2.......18......9.......13.....26
Row 3.................................................................
Row 4....47.....53..................................................
Row 5..................26......13.......9......18.......2.....33

Do you see ?

I've left off the decimal places for clarity, and I've made up some data for the second set of bars.
I've also included a blank row - this will make the first M/F bars be adjacent to the first X/Y/Z bars, and then give you a bit of space before the next M/F bar.