Multiple stacked columns chart with lines

[krispo]

Hi all,

I have some experience with charting but I almost died trying to plot
stacked columns chart combining with lines.

My complete dream is to have:
- month by month (so 12) groups of 3 stacks of volumes (each stack of 2
series each)
- two lines in the same time for monthly margin

I've followed Addy Pope's hint for stack bars with different width but
it did not work.

Is my idea crazy or that is possible to do?

Best regards
Krispo

 

[Andy Pope]

Hi,

Easiest way is to use clustered columns. Hopefully the example data
below remains intact.

Start by creating a clustered column chart on all of the data A1:I14
Change the last 2 sets of columns to line charts.

Change the last 3 columns to the secondary axis.
Delete the secondary Y axis so the columns uses the same scale.

In order to get the stacks to work properly you will need to manipulate
the data slightly. Stack1 data will need to include the corresponding
Stack2 value in order to make it appear stacked.

Stack1 Stack2
Clus1 Clus2 Clus3 Clus1 Clus2 Clus3 Line1 Line2
J 2 8 16 1 4 8 12 1
F 4 10 18 2 5 9 11 2
M 6 12 20 3 6 10 10 3
A 8 14 22 4 7 11 9 4
M 10 16 24 5 8 12 8 5
J 12 18 2 6 9 1 7 6
J 14 20 4 7 10 2 6 7
A 16 22 6 8 11 3 5 8
S 18 24 8 9 12 4 4 9
O 20 2 10 10 1 5 3 10
N 22 4 12 11 2 6 2 11
D 24 6 14 12 3 7 1 12

Cheers
Andy

ps: If you see my brother Addy, say hi

 

[krispo]

Hi,

thank you for reply.
I generally catch the idea but don't cope with data manipulatin
because I can not properly read the meaning of sample data you explai
with (unfortunately layout got mixed).

To be more serious I attached the spreadshit and I do appreciate you
help.

Regards
Krispo

P.S. Sorry for typing erro ANDY :

 

[krispo]

Andy,

could you have a look please