Partially stacked column chart with 2 axis

[Maki]

I have a table on water usage and cost as you see below.
For earlier bills, I only have total charge and no break-down data as I have
from Sep-06 onward.

I would like to put the charge(s) in bar/column chart, where there are
break-downs, in stacked bar/column and simple

bar/column where there are only the total.

I'd also like Usage to be on the secondary (vertical) axis. For some
unknown reasons, I've tried having secondary axis,

by specifying the Series "Usage" and Format Selection but the options of
primary or secondary axis simply don't appear

under "Series Options". Horizontal axis would be date.

Could someone help me out on this? I've looked at PTS Blog site but
couldn't quite find the solution for my particular

problem.

A B C D E F G
Bill_Date Usage Charge1 Charge2 Charge3 Charge4 Total_Charg
----------------------------------------------------------------------------------------------------
2-Mar-05 1519 $2,924.78
1-Jun-05 1944 $3,583.53
2-Sep-05 154 $744.16
2-Dec-05 329 $1,119.85
3-Mar-06 1387 $3,060.76
5-Jun-06 2464 $4,977.82
4-Sep-06 1008 $1,520.49 $471.32 $605.95 $2,597.76
4-Dec-06 1877 $3,265.98 $1,032.35 $605.95 $4,904.28
6-Mar-07 1764 $3,069.36 $970.20 $605.95 $4,645.51
1-Jun-07 1498 $2,606.52 $823.90 $605.95 $4,036.37
4-Sep-07 5 $3.65 $2.75 $627.99 $30.02 $664.41
4-Dec-07 1259 $2,885.11 $692.45 $627.99 $140.31 $4,345.86
5-Mar-08 34 $87.38 $18.70 $627.99 $30.06 $764.13
3-Jun-08 239 $614.23 $131.45 $627.99 $48.51 $1,422.18
3-Sep-08 104 $257.28 $17.05 $656.75 $2.79 $933.87
5-Dec-08 54 $105.51 $674.75 $780.26
3-Mar-09 157 $491.68 $674.75 $1,166.43
4-Jun-09 1962 $7,165.11 $674.75 $7,839.86

Thank you.

 

[Maki]

Sorry, the tabulation got all screwed up in the above posting and I don't
know how to fix it.

The explanation is:

-Column A=Bill_Date, B=Usage, C to F=Charge1 to Charge4, G=Total_Charge
-From 2-Mar-05 to 5-Jun-06, no data for Charge1 to Charge4
-From 4-Sep-06 to 1-Jun-07, no data in Charge4
-From 5-Dec-08 to 4-Jun-09, no data in Charge2 and Charge4

Thanks.

 

[Jon Peltier]

Insert a column before the current column G. In this column, put the
total charge for rows that don't have breakdowns in columns C to F. Make
a stacked column chart using columns C to F and the new column G. Where
there are no breakdowns, C-F are zero and don't appear in the stack and
G shows the total. When there are breakdowns, C-F stacks up to the
total, and G is zero and doesn't appear in the stack.

- Jon

 

[Maki]

Thank you so much, Jon.

I'm ashamed of myself for not experimenting enough after reading your blog.
It was late at night and I was in a hurry but that's no excuse...

Again thank you for your assistance.
Greatly appreciated.

Cheers

 

[Jon Peltier]

Sometimes you just need a gentle nudge in the right direction.

- Jon