Double stacked columns - can it be done?

[Mark]

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 Peltier]

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]

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]

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]

Jon,

The =NA() works.

Thanks for your help!

Mark