Stacked bars

[Guest]

I have a bar chart that shows 8 weeks of data, with the weeks being the X
axis; the Y is the amount. There are three bars: total opened this week,
total closed this week and total still open for the year. I have a breakdown
by business unit for the total still open for the year which my boss wants
represented by a stacked bar.

My question is: Can I incorporate ONE stacked bar into this chart? The
other two bars have no breakdown and don't need one. I can't think of a way
to do this.

Any suggestions? Or should I just create a different chart with that
information for him?

 

[Del Cotter]

I have a bar chart that shows 8 weeks of data, with the weeks being the X
axis; the Y is the amount. There are three bars: total opened this week,
total closed this week and total still open for the year. I have a breakdown
by business unit for the total still open for the year which my boss wants
represented by a stacked bar.

My question is: Can I incorporate ONE stacked bar into this chart? The
other two bars have no breakdown and don't need one. I can't think of a way
to do this.

Have each stacked bar contain n+1 series, where n is the number of
business units, and the +1 is the total over all business units. Then
have n of the series contain data for the business unit for one column
and =NA() for the total for two columns, while the last series contains
=NA() for one column and the total data for two columns.


The data block should then look like this:

Col1 Col2 Col3
BU001 999 #N/A #N/A
BU002 999 #N/A #N/A
BU003 999 #N/A #N/A
BU004 999 #N/A #N/A
Total #N/A 999 999

where "999" is whatever numbers you have. Column 1 will look like a
stacked bar, and columns 2 and 3 will be stacked bars, but won't look
like it because all the blocks in the stack but one will be invisible.

 

[Del Cotter]

Have each stacked bar contain n+1 series, where n is the number of
business units, and the +1 is the total over all business units.

Sorry, your first paragraph said you wanted this for multiple weeks, in
which case try a layout like this:

BU001 BU002 BU003 Total
Open 663
Week1 Closed 467
O/S 33 90 93

Open 580
Week2 Closed 690
O/S 274 11 286

Open 342
Week3 Closed 30
O/S 138 257 217

 

[Jon Peltier]

For bar/column charts, you don't need #N/A to represent null values. A blank
will result in an invisible bar/column, and if you use data labels, a blank
won't show up, but "#N/A" will.

- Jon

 

[Guest]

Okay, I'm gonna try it. I'll get back to you.

Sorry, your first paragraph said you wanted this for multiple weeks, in
which case try a layout like this:

BU001 BU002 BU003 Total
Open 663
Week1 Closed 467
O/S 33 90 93

Open 580
Week2 Closed 690
O/S 274 11 286

Open 342
Week3 Closed 30
O/S 138 257 217