Double Stacked Bar with Line Chart

Hello all,

I believe I'm trying to accomplish the impossible, but I'm hoping someone
might have an answer.

I'm trying to create a double stacked bar chart, with a line chart. I've
managed to create a double stacked bar chart, but I can't get a line chart.
I want the line chart to straddle the groups.

If you create a Stacked Bar chart with a Line Chart (on second axis), you'll
see what I'm trying to accomplish. Just imagine 2 stacked bars instead of
one.

Like I said, trying the impossible.

Here's the data setup I've got for two stacked bar charts. It's going to
look ugly, but I hope it makes sense.

_____________stackedbar1__|__stackedbar2_|
______________Jan-07_____|____Feb-07____|
____________Budget_Actual_|_Budget_Actual_|
Development_|_20____10___|__30_____32___|
Test________| 15____7____|__12_____16___|
Qty Delivered_|_____34_____|______32______| -- this would be the line chart

Hope that helps. I'd appreciate any suggestions on how this could work.

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***

It's really pretty easy, if you forget what you think you know about making
charts.

In my sample, I have this data in A3:H6, where [b] denotes a blank cell and
[s] indicates a cell with a single space character:

[b] [s] Jan-07 [b] [s] Feb-07 [b] [s]
[b] [s] Budget Actual [s] Budget Actual [s]
Dev 0 20 10 0 30 32 0
Test 0 15 7 0 12 16 0

I also have this data in J4:L5

[b] 2.5 5.5
Deliv 34 32

Select A3:H6, run the chart wizard, and create a stacked column chart.
Select and copy J4:L5, select the chart, use Edit menu Paste Special to
add the data as a new series, series in rows, first row has categories and
first column has series names.

This adds another column to the stack, not at all where you want it. Select
the added column series, go to Chart menu Chart Type, and choose an XY
type, probably with markers and lines.

Partway there. Now double click the XY series, and on the Axis tab, choose
Primary.

Where did the 2.5 and 5.5 come from? in the chart, count the categories,
including the blank spaces:

blank, budget, actual, blank, budget, actual, blank.

So categories 1, 4, and 7 are blanks. To get a marker straddling the budget
and actual, we want a marker between categories 2 and 3, and between
categories 5 and 6. That translates to X values of 2.5 and 5.5, which I had
placed in cells K5 and L5.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jay" wrote in message
...
Hello all,

I believe I'm trying to accomplish the impossible, but I'm hoping someone
might have an answer.

I'm trying to create a double stacked bar chart, with a line chart. I've
managed to create a double stacked bar chart, but I can't get a line
chart.
I want the line chart to straddle the groups.

If you create a Stacked Bar chart with a Line Chart (on second axis),
you'll
see what I'm trying to accomplish. Just imagine 2 stacked bars instead of
one.

Like I said, trying the impossible.

Here's the data setup I've got for two stacked bar charts. It's going to
look ugly, but I hope it makes sense.

_____________stackedbar1__|__stackedbar2_|
______________Jan-07_____|____Feb-07____|
____________Budget_Actual_|_Budget_Actual_|
Development_|_20____10___|__30_____32___|
Test________| 15____7____|__12_____16___|
Qty Delivered_|_____34_____|______32______| -- this would be the line
chart

Hope that helps. I'd appreciate any suggestions on how this could work.

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***

I must be doing something incorrectly.

My line chart appears, but in category 1 and 2. It won't cross multiple
categories.

Does your line chart cross multiple categories?

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***


"Jon Peltier" wrote:

It's really pretty easy, if you forget what you think you know about making
charts.

In my sample, I have this data in A3:H6, where [b] denotes a blank cell and
[s] indicates a cell with a single space character:

[b] [s] Jan-07 [b] [s] Feb-07 [b] [s]
[b] [s] Budget Actual [s] Budget Actual [s]
Dev 0 20 10 0 30 32 0
Test 0 15 7 0 12 16 0

I also have this data in J4:L5

[b] 2.5 5.5
Deliv 34 32

Select A3:H6, run the chart wizard, and create a stacked column chart.
Select and copy J4:L5, select the chart, use Edit menu Paste Special to
add the data as a new series, series in rows, first row has categories and
first column has series names.

This adds another column to the stack, not at all where you want it. Select
the added column series, go to Chart menu Chart Type, and choose an XY
type, probably with markers and lines.

Partway there. Now double click the XY series, and on the Axis tab, choose
Primary.

Where did the 2.5 and 5.5 come from? in the chart, count the categories,
including the blank spaces:

blank, budget, actual, blank, budget, actual, blank.

So categories 1, 4, and 7 are blanks. To get a marker straddling the budget
and actual, we want a marker between categories 2 and 3, and between
categories 5 and 6. That translates to X values of 2.5 and 5.5, which I had
placed in cells K5 and L5.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jay" wrote in message
...
Hello all,

I believe I'm trying to accomplish the impossible, but I'm hoping someone
might have an answer.

I'm trying to create a double stacked bar chart, with a line chart. I've
managed to create a double stacked bar chart, but I can't get a line
chart.
I want the line chart to straddle the groups.

If you create a Stacked Bar chart with a Line Chart (on second axis),
you'll
see what I'm trying to accomplish. Just imagine 2 stacked bars instead of
one.

Like I said, trying the impossible.

Here's the data setup I've got for two stacked bar charts. It's going to
look ugly, but I hope it makes sense.

_____________stackedbar1__|__stackedbar2_|
______________Jan-07_____|____Feb-07____|
____________Budget_Actual_|_Budget_Actual_|
Development_|_20____10___|__30_____32___|
Test________| 15____7____|__12_____16___|
Qty Delivered_|_____34_____|______32______| -- this would be the line
chart

Hope that helps. I'd appreciate any suggestions on how this could work.

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***

Don't make a line chart of the last series, make an XY chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jay" wrote in message
...
I must be doing something incorrectly.

My line chart appears, but in category 1 and 2. It won't cross multiple
categories.

Does your line chart cross multiple categories?

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***


"Jon Peltier" wrote:

It's really pretty easy, if you forget what you think you know about
making
charts.

In my sample, I have this data in A3:H6, where [b] denotes a blank cell
and
[s] indicates a cell with a single space character:

[b] [s] Jan-07 [b] [s] Feb-07 [b] [s]
[b] [s] Budget Actual [s] Budget Actual [s]
Dev 0 20 10 0 30 32 0
Test 0 15 7 0 12 16 0

I also have this data in J4:L5

[b] 2.5 5.5
Deliv 34 32

Select A3:H6, run the chart wizard, and create a stacked column chart.
Select and copy J4:L5, select the chart, use Edit menu Paste Special to
add the data as a new series, series in rows, first row has categories
and
first column has series names.

This adds another column to the stack, not at all where you want it.
Select
the added column series, go to Chart menu Chart Type, and choose an XY
type, probably with markers and lines.

Partway there. Now double click the XY series, and on the Axis tab,
choose
Primary.

Where did the 2.5 and 5.5 come from? in the chart, count the categories,
including the blank spaces:

blank, budget, actual, blank, budget, actual, blank.

So categories 1, 4, and 7 are blanks. To get a marker straddling the
budget
and actual, we want a marker between categories 2 and 3, and between
categories 5 and 6. That translates to X values of 2.5 and 5.5, which I
had
placed in cells K5 and L5.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jay" wrote in message
...
Hello all,

I believe I'm trying to accomplish the impossible, but I'm hoping
someone
might have an answer.

I'm trying to create a double stacked bar chart, with a line chart.
I've
managed to create a double stacked bar chart, but I can't get a line
chart.
I want the line chart to straddle the groups.

If you create a Stacked Bar chart with a Line Chart (on second axis),
you'll
see what I'm trying to accomplish. Just imagine 2 stacked bars instead
of
one.

Like I said, trying the impossible.

Here's the data setup I've got for two stacked bar charts. It's going
to
look ugly, but I hope it makes sense.

_____________stackedbar1__|__stackedbar2_|
______________Jan-07_____|____Feb-07____|
____________Budget_Actual_|_Budget_Actual_|
Development_|_20____10___|__30_____32___|
Test________| 15____7____|__12_____16___|
Qty Delivered_|_____34_____|______32______| -- this would be the line
chart

Hope that helps. I'd appreciate any suggestions on how this could
work.

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***

That was exactly my issue. Read what I wanted and not what you wrote.

That awesome, thanks! I really appreciate it. There's no way I would have
figured that out myself.

Cheers,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***


"Jon Peltier" wrote:

Don't make a line chart of the last series, make an XY chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jay" wrote in message
...
I must be doing something incorrectly.

My line chart appears, but in category 1 and 2. It won't cross multiple
categories.

Does your line chart cross multiple categories?

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***


"Jon Peltier" wrote:

It's really pretty easy, if you forget what you think you know about
making
charts.

In my sample, I have this data in A3:H6, where [b] denotes a blank cell
and
[s] indicates a cell with a single space character:

[b] [s] Jan-07 [b] [s] Feb-07 [b] [s]
[b] [s] Budget Actual [s] Budget Actual [s]
Dev 0 20 10 0 30 32 0
Test 0 15 7 0 12 16 0

I also have this data in J4:L5

[b] 2.5 5.5
Deliv 34 32

Select A3:H6, run the chart wizard, and create a stacked column chart.
Select and copy J4:L5, select the chart, use Edit menu Paste Special to
add the data as a new series, series in rows, first row has categories
and
first column has series names.

This adds another column to the stack, not at all where you want it.
Select
the added column series, go to Chart menu Chart Type, and choose an XY
type, probably with markers and lines.

Partway there. Now double click the XY series, and on the Axis tab,
choose
Primary.

Where did the 2.5 and 5.5 come from? in the chart, count the categories,
including the blank spaces:

blank, budget, actual, blank, budget, actual, blank.

So categories 1, 4, and 7 are blanks. To get a marker straddling the
budget
and actual, we want a marker between categories 2 and 3, and between
categories 5 and 6. That translates to X values of 2.5 and 5.5, which I
had
placed in cells K5 and L5.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jay" wrote in message
...
Hello all,

I believe I'm trying to accomplish the impossible, but I'm hoping
someone
might have an answer.

I'm trying to create a double stacked bar chart, with a line chart.
I've
managed to create a double stacked bar chart, but I can't get a line
chart.
I want the line chart to straddle the groups.

If you create a Stacked Bar chart with a Line Chart (on second axis),
you'll
see what I'm trying to accomplish. Just imagine 2 stacked bars instead
of
one.

Like I said, trying the impossible.

Here's the data setup I've got for two stacked bar charts. It's going
to
look ugly, but I hope it makes sense.

_____________stackedbar1__|__stackedbar2_|
______________Jan-07_____|____Feb-07____|
____________Budget_Actual_|_Budget_Actual_|
Development_|_20____10___|__30_____32___|
Test________| 15____7____|__12_____16___|
Qty Delivered_|_____34_____|______32______| -- this would be the line
chart

Hope that helps. I'd appreciate any suggestions on how this could
work.

Thanks,
Jay
--
Disregard, this is so I can find my post later.
***postedbyJay***