Prev: Excel 2007 - VBA - set xaxis orientation
Next: Excel 2007 crashing with simple scatter plots with 8 k data po
From: cherman on 24 Feb 2010 20:44
I'm using Excel 2003. I have basically 2 sets of data, the 1st is a total
count of people and the other set is a breakdown of the total count into 5
subcategories. I have a stacked column chart with a weekly category axis that
has the 5 categories and the total all in one column per week. However, the
count is off because it's adding the total and the 5 subcategories. I only
want the series value to be the total.

For example, if my total is 500 then it is showing 1000 for the series value
because it's adding the total and the subcategories (subcategories always add
up to the total, so it's doubling).

I have the source data set up with the total and each subcategory in
separate columns.

Is there any way to get what I want? I would even consider having 2 columns
on the chart, one for the total and the other stacked with the 5 categories,
but I really would like it all in 1 column.

Thanks!
From: Bernard Liengme on 25 Feb 2010 09:42
What you describe is the way STACKED chart are design to work.
If A has value 20, B and value 60, then A stacked on B will end up as 80
from origin
The combined column is the sum of the parts.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"cherman" <cherman(a)discussions.microsoft.com> wrote in message
news:3D0A6DB1-CA13-4E01-BA99-A103DB3454B0(a)microsoft.com...
> I'm using Excel 2003. I have basically 2 sets of data, the 1st is a total
> count of people and the other set is a breakdown of the total count into 5
> subcategories. I have a stacked column chart with a weekly category axis
> that
> has the 5 categories and the total all in one column per week. However,
> the
> count is off because it's adding the total and the 5 subcategories. I only
> want the series value to be the total.
>
> For example, if my total is 500 then it is showing 1000 for the series
> value
> because it's adding the total and the subcategories (subcategories always
> add
> up to the total, so it's doubling).
>
> I have the source data set up with the total and each subcategory in
> separate columns.
>
> Is there any way to get what I want? I would even consider having 2
> columns
> on the chart, one for the total and the other stacked with the 5
> categories,
> but I really would like it all in 1 column.
>
> Thanks!

From: AdamV on 25 Feb 2010 10:45
I can't see why you would want a stack showing all five categories and
the total stacked on top of that, it makes no real sense.
However, you should be able to show the total and plot it on a secondary
axis, change the chart type to a non-stacked column, add data labels to
show the total values, hide the columns (no border, no fill), then
delete the second axis to force it to a single scale.

Hope this helps
Adam

On 25/02/2010 01:44, cherman wrote:
> I'm using Excel 2003. I have basically 2 sets of data, the 1st is a total
> count of people and the other set is a breakdown of the total count into 5
> subcategories. I have a stacked column chart with a weekly category axis that
> has the 5 categories and the total all in one column per week. However, the
> count is off because it's adding the total and the 5 subcategories. I only
> want the series value to be the total.
>
> For example, if my total is 500 then it is showing 1000 for the series value
> because it's adding the total and the subcategories (subcategories always add
> up to the total, so it's doubling).
>
> I have the source data set up with the total and each subcategory in
> separate columns.
>
> Is there any way to get what I want? I would even consider having 2 columns
> on the chart, one for the total and the other stacked with the 5 categories,
> but I really would like it all in 1 column.
>
> Thanks!
From: cherman on 25 Feb 2010 11:55
Thanks for your feedback, and I agree with you on what a stacked column chart
is supposed to show. I had a talk with my boss and that is what he wanted to
see. Anyway, thanks for the tip!

"AdamV" wrote:

> I can't see why you would want a stack showing all five categories and
> the total stacked on top of that, it makes no real sense.
> However, you should be able to show the total and plot it on a secondary
> axis, change the chart type to a non-stacked column, add data labels to
> show the total values, hide the columns (no border, no fill), then
> delete the second axis to force it to a single scale.
>
> Hope this helps
> Adam
>
> On 25/02/2010 01:44, cherman wrote:
> > I'm using Excel 2003. I have basically 2 sets of data, the 1st is a total
> > count of people and the other set is a breakdown of the total count into 5
> > subcategories. I have a stacked column chart with a weekly category axis that
> > has the 5 categories and the total all in one column per week. However, the
> > count is off because it's adding the total and the 5 subcategories. I only
> > want the series value to be the total.
> >
> > For example, if my total is 500 then it is showing 1000 for the series value
> > because it's adding the total and the subcategories (subcategories always add
> > up to the total, so it's doubling).
> >
> > I have the source data set up with the total and each subcategory in
> > separate columns.
> >
> > Is there any way to get what I want? I would even consider having 2 columns
> > on the chart, one for the total and the other stacked with the 5 categories,
> > but I really would like it all in 1 column.
> >
> > Thanks!
> .
>
From: cherman on 25 Feb 2010 17:55
Yes, I agree, but my boss wanted to see the total of all the segments as well
as the segments. The end result makes sense to me, but the stacked column
chart isn't designed that way, so I must figure out another way to provide
the total.

Thanks!

"Bernard Liengme" wrote:

> What you describe is the way STACKED chart are design to work.
> If A has value 20, B and value 60, then A stacked on B will end up as 80
> from origin
> The combined column is the sum of the parts.
> best wishes
> --
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
>
> "cherman" <cherman(a)discussions.microsoft.com> wrote in message
> news:3D0A6DB1-CA13-4E01-BA99-A103DB3454B0(a)microsoft.com...
> > I'm using Excel 2003. I have basically 2 sets of data, the 1st is a total
> > count of people and the other set is a breakdown of the total count into 5
> > subcategories. I have a stacked column chart with a weekly category axis
> > that
> > has the 5 categories and the total all in one column per week. However,
> > the
> > count is off because it's adding the total and the 5 subcategories. I only
> > want the series value to be the total.
> >
> > For example, if my total is 500 then it is showing 1000 for the series
> > value
> > because it's adding the total and the subcategories (subcategories always
> > add
> > up to the total, so it's doubling).
> >
> > I have the source data set up with the total and each subcategory in
> > separate columns.
> >
> > Is there any way to get what I want? I would even consider having 2
> > columns
> > on the chart, one for the total and the other stacked with the 5
> > categories,
> > but I really would like it all in 1 column.
> >
> > Thanks!
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Excel 2007 - VBA - set xaxis orientation
Next: Excel 2007 crashing with simple scatter plots with 8 k data po