Partially stacked column chart with 2 axis

  • Thread starter Thread starter Maki
  • Start date Start date
M

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.
 
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.
 
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
 
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
 
Back
Top