Cluster stacked graph AND line combo graph?

[robotman]

Is there a way to create a cluster stacked bar graph AND a line combo
graph all in one chart?

My data layout:

Set 1 - Stacked Bar - Showing values A and B
Set 2 - Stacked Bar - Showing values C and D
Set 3 - Line graph with line connection

These 3 sets would each be in one category. For example, I would want
to show Q1 with all 3 sets, Q2 with all 3 sets... etc.

Hope that makes sense. Any ideas?!

Thanks!

 

[Ed Ferrero]

Hi,

There's a thing called 'Columns and Lines' at
http://www.edferrero.com/charting.aspx that may do what you want.

Ed Ferrero

 

[robotman]

What I need is cluster STACED bar graphs. Those examples seem to be
just cluster bars and line charts.

I figured a way to do it by charting all the stacked bar data first and
then adding a new series, changing that to the 2ndary axis, and
changing that chart type to line.

However, the line doesn't plot in the middle of the stacked cluster
bars.

Does anyone have any ideas how to adjust the positioning of the line
part of the graph? Since I only have 2 stacked bars in each cluster,
it seems I'm forced to plot the line with either the 1st or 2nd bar.

Thanks!

 

[robotman]

What I need is cluster STACKED bar graphs. Those examples seem to be
just cluster bars and line charts.

I figured a way to do it by charting all the stacked bar data first and
then adding a new series, changing that to the 2ndary axis, and
changing that chart type to line.

However, the line doesn't plot in the middle of the stacked cluster
bars.

Does anyone have any ideas how to adjust the positioning of the line
part of the graph? Since I only have 2 stacked bars in each cluster,
it seems I'm forced to plot the line with either the 1st or 2nd bar.

Thanks!

 

[Jon Peltier]

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

- Jon

 

[robotman]

Thanks for the website. Nice instructions.

A couple issues for me.

1. I want STACKED cluster bars. The examples all are solid bars. I
think the general same strategy works, though. You just need to start
off with a stacked bar cluster chart and convert some of the series to
lines. You can fill in the gaps with =N/A() to connect the lines.

2. If you have an even number of bars, you can't make the lines fall
in between the bars (as far as I can tell). Maybe someone has figured
out a way to insert a dummy series between the real ones so the line
can match that one?

Thanks!

 

[Jon Peltier]

For the clustered stacked bars:

http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html

Combine this with the clustered column and line example:

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

- Jon