Combination chart? Stacked cylinders and scatter

  • Thread starter Thread starter sdubose99
  • Start date Start date
S

sdubose99

Hi, I want to make a stacked bar chart that also shows another data
point as a single point along the stacked bar. Here is what I have so
far

'[img=[URL]http://img304.imageshack.us/img304/5372/chart7ur.th.jpg[/URL]]'
(http://img304.imageshack.us/my.php?image=chart7ur.jpg)

Here is what I want to accomplish:

- don't show Sections where Length is zero -- right now the zero
sections show with label and value on the right side of the cylinders
- show another data point (from another cell in my sheet) as a single
point along the stacked bar
- show a final data point as the total of the bar lengths.

I appreciate any help or direction you can give!

thanks,
Scott :)
 
Scott -

You cannot make a combination chart if one of the chart types has 3D
effects. Which is one way to prevent people from using chart types with 3D
effects.

Use a regular stacked bar chart. Then copy the XY data to add to this chart,
select the chart, and use Paste Special from the Edit menu to add it to the
chart as a new series. It will be added as another stacked bar series, but
select the new series, and from the Chart menu, choose Chart Type, and
change it to an XY type.

This will probably put the XY points is a strange place. You need to remove
the secondary X axis (by default at the top of the chart) and the XY series
will use the primary Y axis (bottom of the chart by default) for its X
values. Then note that the Y values for the XY series should be 1, 2, 3,
where 1 is the first stack of bars from the bottom, 2 the second, etc.
Double click the primary Y axis (by default on the left) and set the scale
parameters to minimum 0.5 and maximum N + 0.5, where N is the number of
stacks of bars (maximum 1.5 in your posted chart).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon, thanks very much. It helps to know I wasn't going completely craz
-- but I thought I was...

Here's how far I got.

http://img221.imageshack.us/my.php?image=chart8jo.jpg

Is there a way to share the axis on the bottom so the new data point i
in the proper relation to the other data? I thought about making th
min and max scale match the bottom axis, but the scale on the botto
will increase or decrease depending on what the user enters in th
"Length" cells.

Is there a way to not show the zero values at the right side -- Serie
4 and 5?

Scot
 
sdubose99 said:
Jon, thanks very much. It helps to know I wasn't going completely crazy
-- but I thought I was...

Here's how far I got.

http://img221.imageshack.us/my.php?image=chart8jo.jpg

Is there a way to share the axis on the bottom so the new data point is
in the proper relation to the other data? I thought about making the
min and max scale match the bottom axis, but the scale on the bottom
will increase or decrease depending on what the user enters in the
"Length" cells.

If you select the top axis and press Delete, the XY points will use the
bottom axis.
Is there a way to not show the zero values at the right side -- Series
4 and 5?

Build your own custom labels in a column next to the data, concatenating the
individual words to make the label, but incorporating an IF to turn it to ""
if the value is zero:

=IF(C5=0,"","Section "&B5&CHAR(10)&C5)

CHAR(10) is the linefeed character (or carriage return, I forget which, but
it breaks the label into two lines). Now use one of these fine third party
add-ins to apply this range of labels to the stacked bar series:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com/ss

Both are free and easy to use.

Actually, since your series are one point each, set up the labels in a
column to the left of the values, and include this column in the source data
range, so Excel uses them as series names. Then use the Show Series Names
option for data labels, and you don't need a chart labeling add-in, at least
not for this.

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