Combined Stacked Column Graph

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Mar 28, 2004

• #2

hi - welcome to the board!

first port of call for graph problems (for me, at least) is:

http://peltiertech.com/Excel/Charts/index.html

...if you're still having problems, post back with details re what data you've got & how it's set up on the sheet & we'll see what we can do...

 

Upvote 0

B

Bounty Hunter

New Member

Joined

Mar 28, 2004

Messages

7

• Mar 28, 2004

• #3

G'day Paddy, thanks for the link. I have actually been there and there is no solution to this particular problem.

I can set the data up any way I like at this stage so that is not so much the issue.

The problem is that I can stack 1 bonus number on a corresponding salary, and I can potentially do a second using a secondary axis but then how do I get 1 more stacked column graph in there? Everytime I try this it simply stacks all data together.

Thanks in advance!

[/img]

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Mar 28, 2004

• #4

ok - i'm still not sure I get you problem.

with the following data:

Book2
	A	B	C	D	E	F
1		Salary	Q1Bonus	Q2Bonus	Q3Bonus	Q4Bonus
2	Tom	100	5	10	10	10
3	****	150	10	5	10	15
4	Harry	120	5	5	5	15
Sheet3

...do the following:

1) select the area in question & hit the charticon on the toolbar.
2) select column charts & the 'stacked column' sub-type (second one of the column chart types) hit next
3) on the chart source data' step of the wizard, te "series in" defaults to rows (i.e. salary, q1 bonus etc is plotted on the x axis with vlues for Tom **** & Harry). Select column to get the opposite (& desired) effect.

result is 3 columns, one for each person, with 5 areas for salary & th 4 bonuses. is this what you're after? if not, please describe your data etc...

 

Upvote 0

B

Bounty Hunter

New Member

Joined

Mar 28, 2004

Messages

7

• Mar 28, 2004

• #5

Paddy, not quite that simple........

Can you tell me how to insert the excel picture in the message and I will show you what I mean?

Thanks again!

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Mar 28, 2004

• #6

look for the link to colo's html maker addin below this post.

 

Upvote 0

B

Bounty Hunter

New Member

Joined

Mar 28, 2004

Messages

7

• Mar 28, 2004

• #7

Unfortunately I can't download this application from work PC. I will try to explain if you can bear with me:

It is the same as your data below for columns A, B & C, but then

column D = Median Market Salary
column E = Median Market Bonus.

There is then 2 more columns:

F = 75th percentile mkt salary
G = 75th percentile mkt bonus.

So there are 3 stacked column graphs from this data where C is on top of B, E is on top of D, and G is on top of F.

This allows a comparison of employees salary, bonus and total compensation (the addition of both - stacked) to market median and 75th percentile while using minimal space - only 3 columns.

So (for example) I can compare 3 employees salary, bonus and total compensation on 1 graph using only 9 columns.

Does this make sense? I hope so!

Cheers

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Mar 29, 2004

• #8

OK, but it requires a bit of a weird data set up.

see the example for how the original needs to be arranged:

Book2
	A	B	C	D	E	F	G
1		Salary	Bonus	MedianSalary	MedianBonus	75Salay	75Bonus
2	Tom	100	5	110	7	160	12
3	****	150	10	110	7	160	12
4	Harry	120	5	110	7	160	12
5
6
7	X	Salary	Bonus	MedianSalary	MedianBonus	75Salay	75Bonus
8	Tom	100	5
9				110	7
10						160	12
11	****	150	10
12				110	7
13						160	12
14	Harry	120	5
15				110	7
16						160	12
Sheet3

...basically, between each value of the x axis, you need as many rows as columns you want stacked.


an example of the method can be found here:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q214119

 

Upvote 0

B

Bounty Hunter

New Member

Joined

Mar 28, 2004

Messages

7

• Mar 29, 2004

• #9

That is absolutely perfect! Thanks very much for your help Paddy!!!

 

Upvote 0

J

Jon Peltier

MrExcel MVP

Joined

May 14, 2003

Messages

5,320

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Mar 29, 2004

• #10

There is a tutorial showing this technique on Bernard Liengme's site:

http://www.stfx.ca/people/bliengme/ExcelTips/Columns.htm

which is based on the example on Stephen Bullen's web site:

http://www.bmsltd.ie/Excel/Default.htm
Go to Charting Examples, and download FunChart4.xls

The following MSKB article has an example:

XL2000: Creating Charts with Multiple Groups of Stacked Bars
http://support.microsoft.com/default.aspx?scid=kb;en-ca;214119

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

 

Upvote 0