Help!

Double stacked columns - can it be done?

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Charting RSS
Next:  Background picture behind chart  
Author Message
Mark
External


Since: Jul 13, 2004
Posts: 271



PostPosted: Wed Nov 16, 2005 3:59 pm    Post subject: Double stacked columns - can it be done?
Archived from groups: microsoft>public>excel>charting (more info?)

I need 2 stacked columns side by side for each month.

Here is the data for October:
Forecast Credits 200
Forecast Cash 50
Actual Credits 220
Actual Cash 55
Total Forecast 250
Total Actual 275

One column is forecast; the other is actual.
I would then need a line plotted on the secondary axis for Total Actual.

Can it be done?
Back to top
Jon Peltier
External


Since: Dec 03, 2004
Posts: 1684



PostPosted: Thu Nov 17, 2005 1:56 am    Post subject: Re: Double stacked columns - can it be done? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

So you would like a clustered-stacked column chart? Excel doesn't do
this type of chart natively, but with a little clever arrangement of the
data you can make a stacked chart look the way you want. This page links
to a handful of examples:

http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html

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


Mark wrote:
> I need 2 stacked columns side by side for each month.
>
> Here is the data for October:
> Forecast Credits 200
> Forecast Cash 50
> Actual Credits 220
> Actual Cash 55
> Total Forecast 250
> Total Actual 275
>
> One column is forecast; the other is actual.
> I would then need a line plotted on the secondary axis for Total Actual.
>
> Can it be done?
Back to top
Mark
External


Since: Jul 13, 2004
Posts: 271



PostPosted: Thu Nov 17, 2005 3:08 pm    Post subject: Re: Double stacked columns - can it be done? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Thank you! It worked.

Now, I am trying to plot a "running" total across the 12 months, but becuase
I have 2 lines for each month, "credits & cash." Some lines for the total are
blank, but are plotted anyway.

How do I get around this?

Mark

"Jon Peltier" wrote:

> So you would like a clustered-stacked column chart? Excel doesn't do
> this type of chart natively, but with a little clever arrangement of the
> data you can make a stacked chart look the way you want. This page links
> to a handful of examples:
>
> http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Mark wrote:
> > I need 2 stacked columns side by side for each month.
> >
> > Here is the data for October:
> > Forecast Credits 200
> > Forecast Cash 50
> > Actual Credits 220
> > Actual Cash 55
> > Total Forecast 250
> > Total Actual 275
> >
> > One column is forecast; the other is actual.
> > I would then need a line plotted on the secondary axis for Total Actual.
> >
> > Can it be done?
>
Back to top
Jon Peltier
External


Since: Sep 25, 2005
Posts: 212



PostPosted: Thu Nov 17, 2005 6:52 pm    Post subject: Re: Double stacked columns - can it be done? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Do you mean: rather than skipping blanks or drawing a line across the gap,
the chart plots a zero? This is because you have a formula which most likely
is returning "". Excel treats text numerically as zero, and "" is really
just a short string. Use NA() instead of "" in your formula. It puts an ugly
#N/A error in the sheet, but the chart interpolates a line across this
blank. Well, a line or XY chart does; other chart types aren't so clever.

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

"Mark" wrote in message

> Thank you! It worked.
>
> Now, I am trying to plot a "running" total across the 12 months, but
> becuase
> I have 2 lines for each month, "credits & cash." Some lines for the total
> are
> blank, but are plotted anyway.
>
> How do I get around this?
>
> Mark
>
> "Jon Peltier" wrote:
>
>> So you would like a clustered-stacked column chart? Excel doesn't do
>> this type of chart natively, but with a little clever arrangement of the
>> data you can make a stacked chart look the way you want. This page links
>> to a handful of examples:
>>
>> http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services
>> Tutorials and Custom Solutions
>> http://PeltierTech.com/
>> _______
>>
>>
>> Mark wrote:
>> > I need 2 stacked columns side by side for each month.
>> >
>> > Here is the data for October:
>> > Forecast Credits 200
>> > Forecast Cash 50
>> > Actual Credits 220
>> > Actual Cash 55
>> > Total Forecast 250
>> > Total Actual 275
>> >
>> > One column is forecast; the other is actual.
>> > I would then need a line plotted on the secondary axis for Total
>> > Actual.
>> >
>> > Can it be done?
>>
Back to top
Mark
External


Since: Jul 13, 2004
Posts: 271



PostPosted: Thu Nov 17, 2005 6:52 pm    Post subject: Re: Double stacked columns - can it be done? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Jon,

The =NA() works.

Thanks for your help!

Mark

"Jon Peltier" wrote:

> Do you mean: rather than skipping blanks or drawing a line across the gap,
> the chart plots a zero? This is because you have a formula which most likely
> is returning "". Excel treats text numerically as zero, and "" is really
> just a short string. Use NA() instead of "" in your formula. It puts an ugly
> #N/A error in the sheet, but the chart interpolates a line across this
> blank. Well, a line or XY chart does; other chart types aren't so clever.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> "Mark" wrote in message
>
> > Thank you! It worked.
> >
> > Now, I am trying to plot a "running" total across the 12 months, but
> > becuase
> > I have 2 lines for each month, "credits & cash." Some lines for the total
> > are
> > blank, but are plotted anyway.
> >
> > How do I get around this?
> >
> > Mark
> >
> > "Jon Peltier" wrote:
> >
> >> So you would like a clustered-stacked column chart? Excel doesn't do
> >> this type of chart natively, but with a little clever arrangement of the
> >> data you can make a stacked chart look the way you want. This page links
> >> to a handful of examples:
> >>
> >> http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html
> >>
> >> - Jon
> >> -------
> >> Jon Peltier, Microsoft Excel MVP
> >> Peltier Technical Services
> >> Tutorials and Custom Solutions
> >> http://PeltierTech.com/
> >> _______
> >>
> >>
> >> Mark wrote:
> >> > I need 2 stacked columns side by side for each month.
> >> >
> >> > Here is the data for October:
> >> > Forecast Credits 200
> >> > Forecast Cash 50
> >> > Actual Credits 220
> >> > Actual Cash 55
> >> > Total Forecast 250
> >> > Total Actual 275
> >> >
> >> > One column is forecast; the other is actual.
> >> > I would then need a line plotted on the secondary axis for Total
> >> > Actual.
> >> >
> >> > Can it be done?
> >>
>
>
>
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Charting All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum