Thread: Double stacked columns - can it be done?

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?

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/ChartsH...sterStack.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?

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/ChartsH...sterStack.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?
>

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" <Mark@discussions.microsoft.com> wrote in message
news:9FD1911C-9B79-419B-A6AA-B6A0F0FC7DFE@microsoft.com...
> 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/ChartsH...sterStack.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?
>>

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" <Mark@discussions.microsoft.com> wrote in message
> news:9FD1911C-9B79-419B-A6AA-B6A0F0FC7DFE@microsoft.com...
> > 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/ChartsH...sterStack.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?
> >>
>
>
>