Dynamically stacking columns by contribution %

I'm trying to create a stacked column chart that contains
many stacked elements. Y-axis is Volume, X-axis is time
(say, month), and the stacked elements are different
products contributing to the overall volume for the
month. I'm interested in seeing each stack in the order
of largest to smallest contributor to volume for each
month. Thus, the stack order would change from month to
month. I'm not interested in whether the legend data
series order matches the stack orders (it can't since the
series order on the stack columns change from month to
month). How could I do this?

Also, how do I selectively show which dataseries to show
on a legend without deleting the dataseries from the
chart?

Thanks.

Sam

Hi Sam,

One way to do this is to add multiple data series for each possible
position of a product within each month.
I have created an example and posted it here,
(http://www.andypope.info/ngs/ng20.htm)

If the number of products is large then maybe a VBA approach would be
better. Post back is this is the case.

In order to remove legend entries from the legend you select the legend,
then select the individual legend entry before pressing delete.
Make sure it is the legend entry that is selected and not the legend
marker. As deleting the legend marker will remove the data series from
the chart.

Sam W wrote:

I'm trying to create a stacked column chart that contains
many stacked elements. Y-axis is Volume, X-axis is time
(say, month), and the stacked elements are different
products contributing to the overall volume for the
month. I'm interested in seeing each stack in the order
of largest to smallest contributor to volume for each
month. Thus, the stack order would change from month to
month. I'm not interested in whether the legend data
series order matches the stack orders (it can't since the
series order on the stack columns change from month to
month). How could I do this?

Also, how do I selectively show which dataseries to show
on a legend without deleting the dataseries from the
chart?

Thanks.

Sam

--

Cheers
Andy

http://www.andypope.info